sql - Display Parent-Child relationship when Parent and Child are stored in same table

Question

I have SQL Server table structure like below:

ID    Name     ParentID
-----------------------
1     Root       NULL
2     Business   1
3     Finance    1
4     Stock      3

I want to display the details in my web page like

ID    Name     ParentName
-------------------------
1     Root      -
2     Business  Root
3     Finance   Root
4     Stock     Finance    

How can I construct my SQL query?

Answer 1

try this...

SELECT a.ID, a.Name, b.Name AS 'ParentName'
FROM TABLE AS a LEFT JOIN TABLE AS b on a.ParentID = b.ID

With the left join, the query will not find anything to join for the NULL and return blank for the ParentName column.

EDIT:

If you do not want the 'Parent' column to be blank, but want to show a '-' dash then use this query.

SELECT a.ID, a.Name, COALESCE(b.Name,'-') AS 'ParentName'
FROM TABLE AS a LEFT JOIN TABLE AS b on a.ParentID = b.ID