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
Advertisement
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
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.