How to find the Employee names and their supervisor names if the table doesn't have common nume-CodePudding

If the table have only two columns with employee name and their supervisor column and if it doesn't have any other numeric or number column with employee_number or employee_id, then how the results can be produced. I'm not getting logic to show the results.

Code for creating table in Mysql:

    CREATE TABLE DATABASE_TABLE 
    (
      Employee_Name nvarchar(255) PRIMARY KEY,
      Supervisor_Name nvarchar(255) NOT NULL
    );
    
    CREATE INDEX ix_database_table_supervisor
        ON DATABASE_TABLE (Supervisor_Name);
    
    
    INSERT INTO DATABASE_TABLE
(Employee_Name, Supervisor_Name) VALUES 
  ('Alice','Dave'), ('Olive','Dave'), ('Barton','Dave')
, ('Almira','Jacob'), ('Charles','Jacob'), ('Davis','Jacob')
, ('Robert','Risha'), ('Peter','Risha'), ('Ethel','Risha')
, ('Isaac','Jospeh'), ('Sophia','Jospeh'), ('Rosa','Jospeh')
, ('Joshua','Dandy'), ('Silas','Dandy'), ('Fred','Dandy')
, ('Frank','Andrew'), ('Howard','Andrew'), ('Ralph','Andrew')
, ('Dennis','Henry'), ('Alex','Henry'), ('Floyd','Henry')
, ('Carlos','Nelson'), ('Homer','Nelson'), ('Harold','Nelson')
, ('Leo','Simon'), ('Warren','Simon'), ('Clifford','Simon')
, ('Martha','Casper'), ('Hazel','Casper'), ('Irene','Casper')
, ('Dave','Betsy'), ('Jacob','Betsy'), ('Risha','David')
, ('Jospeh','David'), ('Dandy','Phillip'), ('Andrew','Phillip')
, ('Henry','Harvey'), ('Nelson','Harvey'), ('Simon','Paul')
, ('Casper','Paul'), ('Betsy','Joe'), ('David','Joe')
, ('Phillip','Joe'), ('Harvey','Joe'), ('Paul','Joe')

It's output is:

Employee_name   Supervisor_name
Frank           Andrew
Howard          Andrew
Ralph           Andrew
Dave            Betsy
Jacob           Betsy
Hazel           Casper
Irene           Casper
Martha          Casper
Fred            Dandy
Joshua          Dandy
Silas           Dandy
Alice           Dave
Barton          Dave
Olive           Dave
Jospeh          David
Risha           David
Henry           Harvey
Nelson          Harvey
Alex            Henry
Dennis          Henry
Floyd           Henry
Almira          Jacob
Charles         Jacob
Davis           Jacob
Betsy           Joe
David           Joe

....

The result should be in the lower to higher level of hierarchy like:

Employee_Name   Supervisor_Name  Higher_Supervisor  Next_higher_Supervisor
Frank           Andrew           Phillip            Joe
Howard          Andrew           Phillip            Joe
Ralph           Andrew           Phillip            Joe
Dave            Betsy            Joe                no_supervisor
Jacob           Betsy            Joe                no_supervisor
Hazel           Casper           Paul               Joe
Irene           Casper           Paul               Joe
Martha          Casper           Paul               Joe

For Eg: Frank's supervisor is Andrew, Andrew's supervisor is Phillip, Phillip's supervisor is Joe
For Eg: Dave's supervisor is Betsy, Betsy's supervisor is Joe, and Joe doesn't have any supervisor so no_supervisor should be displayed.
For Eg: Hazel's supervisor is Casper, Casper's supervisor's is Paul, and Paul's Supervisor is Joe should be displayed in the order format

CodePudding user response：

For this particular set of data, it can LEFT JOIN the table itself to get the expected results

SELECT a.Employee_Name, a.Supervisor_Name, b.Supervisor_Name, c.Supervisor_Name
FROM DATABASE_TABLE a
LEFT JOIN DATABASE_TABLE b ON a.Supervisor_Name = b.Employee_Name
LEFT JOIN DATABASE_TABLE c ON b.Supervisor_Name = c.Employee_Name

If hierarchy depth is unknown, which means the number of columns is unknown, it's more complicated. It is still possible by using recursive CTE to find the depth and generate dynamic SQL.