I am trying to do self join on table to find desired result.
The issue here is there are 7 level of hierarchy and i have to do 7 self joins to get result. But it is taking tie.
Is there any way we can use other method to resolve it.
QUERY:-
Code:
SELECT DISTINCT
LOWER(G.parent) AS PARENT_NAME,
A.ID
FROM
EMP a
LEFT JOIN EMP B
ON
a.PARENT_ID = B.EMP_ID
LEFT JOIN EMP C
ON
B.PARENT_ID = C.EMP_ID
LEFT JOIN EMP D
ON
C.PARENT_ID = D.EMP_ID
LEFT JOIN EMP E
ON
D.PARENT_ID = E.EMP_ID
LEFT JOIN EMP F
ON
E.PARENT_ID = F.EMP_ID
LEFT JOIN EMP G
ON
F.PARENT_ID = G.EMP_ID