SQL Query - Want to build column based on the values in another column and then use the column to do a left join to get other values

Question

I am working on building an SQL Query where the following tables are available. The Manager and reportee table where the relationship of the manager and reportee is maintained

Person      Subordinate
A            A1
A1           A2
A2               A3
A3               A4

Person      Designation
A       Senior Manager
A1      Manager
A2      Deputy Manager
A3      Assistant Manager
A4      Officer

Designation     Work
Senior Manager      X1
Senior Manager      X2
Manager     X3
Deputy Manager  X4
Deputy Manager  X5

I have written the query which merges table 1 and table 2 as under:

SELECT 
    table1.person AS 'LEVEL_1', table1.subordinate AS 'LEVEL_2', 
    table2.subordinate AS 'LEVEL_3', table3.subordiante AS 'LEVEL_4', 
    table4.subordinate AS 'LEVEL_5', table5.subordinate AS 'LEVEL_6' 
FROM  
    employee_hierarchy table1
LEFT JOIN 
    employee_hierarchy table2 ON table2.person = table1.subordinate
LEFT JOIN 
    employee_hierarchy table3 ON table3.person = table2.subordinate
LEFT JOIN 
    employee_hierarchy table4 ON table4.person = table3.subordinate
LEFT JOIN 
    employee_hierarchy table5 ON table5.person = table4.subordinate
ORDER BY 
    LEVEL_1, LEVEL_2, LEVEL_3, LEVEL_4, LEVEL_5

I want to get the work details against the employee in the lowest level. Can someone help?

question from:https://stackoverflow.com/questions/66067279/sql-query-want-to-build-column-based-on-the-values-in-another-column-and-then

Answer 1

I am changing my answer after seeing your last comment. What I have understood is you want to have work information for Level_2 column. Try this:

with cte as (
SELECT 
    table1.person AS 'LEVEL_1', table1.Subordinate AS 'LEVEL_2', 
    table2.subordinate AS 'LEVEL_3', table3.Subordinate AS 'LEVEL_4', 
    table4.subordinate AS 'LEVEL_5', table5.Subordinate AS 'LEVEL_6' 
FROM  
    employee_hierarchy table1
LEFT JOIN 
    employee_hierarchy table2 ON table2.person = table1.subordinate
LEFT JOIN 
    employee_hierarchy table3 ON table3.person = table2.subordinate
LEFT JOIN 
    employee_hierarchy table4 ON table4.person = table3.subordinate
LEFT JOIN 
    employee_hierarchy table5 ON table5.person = table4.subordinate
    )
    select level_1,level_2,level_3,level_4,level_5,employee.designation,workdetails.work from cte inner join employee 
    on level_2=employee.person
           left join workdetails on employee.designation=workdetails.designation
           ORDER BY     LEVEL_1, LEVEL_2, LEVEL_3, LEVEL_4, LEVEL_5

Hope this will be your desired query:

with cte as (
SELECT 
    table1.person AS 'LEVEL_1', table1.Subordinate AS 'LEVEL_2', 
    table2.subordinate AS 'LEVEL_3', table3.Subordinate AS 'LEVEL_4', 
    table4.subordinate AS 'LEVEL_5', table5.Subordinate AS 'LEVEL_6' 
FROM  
    employee_hierarchy table1
LEFT JOIN 
    employee_hierarchy table2 ON table2.person = table1.subordinate
LEFT JOIN 
    employee_hierarchy table3 ON table3.person = table2.subordinate
LEFT JOIN 
    employee_hierarchy table4 ON table4.person = table3.subordinate
LEFT JOIN 
    employee_hierarchy table5 ON table5.person = table4.subordinate
    )
    select level_1,level_2,level_3,level_4,level_5,employee.designation,workdetails.work from cte inner join employee 
    on (case when level_6 is not null then cte.level_6 when level_5 is not null then level_5 when level_4 is not null then level_4
           when level_3 is not null then level_3 when level_2 is not null then level_2 when level_1 is not null then level_1 end)=employee.person
           left join workdetails on employee.designation=workdetails.designation