How to find the Employee names and their supervisor names if the table doesn’t have common numeric column like Employee_id or employee number in Mysql

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')

​x
 
    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

....

 
Employee_name   Supervisor_nameFrank           AndrewHoward          AndrewRalph           AndrewDave            BetsyJacob           BetsyHazel           CasperIrene           CasperMartha          CasperFred            DandyJoshua          DandySilas           DandyAlice           DaveBarton          DaveOlive           DaveJospeh          DavidRisha           DavidHenry           HarveyNelson          HarveyAlex            HenryDennis          HenryFloyd           HenryAlmira          JacobCharles         JacobDavis           JacobBetsy           JoeDavid           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

 
Employee_Name   Supervisor_Name  Higher_Supervisor  Next_higher_SupervisorFrank           Andrew           Phillip            JoeHoward          Andrew           Phillip            JoeRalph           Andrew           Phillip            JoeDave            Betsy            Joe                no_supervisorJacob           Betsy            Joe                no_supervisorHazel           Casper           Paul               JoeIrene           Casper           Paul               JoeMartha          Casper           Paul               Joe​For Eg: Frank's supervisor is Andrew, Andrew's supervisor is Phillip, Phillip's supervisor is JoeFor 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​

Answer

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

 
SELECT a.Employee_Name, a.Supervisor_Name, b.Supervisor_Name, c.Supervisor_NameFROM DATABASE_TABLE aLEFT JOIN DATABASE_TABLE b ON a.Supervisor_Name = b.Employee_NameLEFT 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.

Advertisement

Answer