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:

    L1.child 'L1', L2.child 'L2', L3.child 'L3', L4.child 'L4', L5.child 'L5'
    #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
    L1.Parent is null

Need Your Help

Java - JButton text disappears if actionPerformed defined afterwards

java swing text action jbutton

This has been bugging me for a while. If I define setText on a JButton before defining setAction, the text disappears:

Why couldn't able to get response from a webpage? Android


I'm very new to Java/Android and I have a code to get the response of a webpage. I have a code like this, but for some reason the BufferedReader's in.readLine() is always returning null.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.