Recursive SQL query to get children under the parents - SQL Server 2008
I'm trying to write query for get the children under the parent id from the table #Temp. Please find the below table #Temp
Note: Parent value is NULL then corresponding child value is the L1 and need to find levels from L1 to L5.
child | Parent c1 p1 p1 NULL c2 p2 p2 p3 p4 p3 p3 NULL
I'm looking for the below output
L1 | L2 | L3 | L4 | L5 p1 c1 NULL NULL NULL p3 p2 c2 NULL NULL p3 p4 NULL NULL NULL
I have tried with this query
SELECT L1.child 'L1', L2.child 'L2', L3.child 'L3', L4.child 'L4', L5.child 'L5' FROM #temp L1 INNER JOIN #temp L2 ON L2.parent = L1.child INNER JOIN #temp L3 ON L3.parent = L2.child INNER JOIN #temp L4 ON L4.parent = L3.child INNER JOIN #temp L5 ON L5.parent = L4.child WHERE L1.parent IS NULL
But I'm not getting expected output. Please advice. Thanks in advance!
change the innerjoin to Left join:
Select L1.child 'L1', L2.child 'L2', L3.child 'L3', L4.child 'L4', L5.child 'L5' from #Temp L1 LEFT join #Temp L2 ON L2.Parent = L1.child LEFT join #Temp L3 ON L3.Parent= L2.child LEFT join #Temp L4 ON L4.Parent= L3.child LEFT join #Temp L5 ON L5.Parent= L4.child where L1.Parent is null