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

Answer 2

Assuming SQL Server 2005+, use a recursive CTE like this:

WITH hierarchy AS (
  SELECT t.id,
         t.name,
         t.parentid,
         CAST(NULL AS VARCHAR(50)) AS parentname
    FROM YOUR_TABLE t
   WHERE t.parentid IS NULL
  UNION ALL
  SELECT x.id,
         x.name,
         x.parentid,
         y.name
    FROM YOUR_TABLE x
    JOIN hierarchy y ON y.id = x.parentid)
SELECT s.id,
       s.name,
       s.parentname
  FROM hierarchy s

The CASTing of the NULL might look odd, but SQL Server defaults the data type to INT unless specified in a manner like you see in my query.