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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…