Returning the parent/ child relationship on a self-joining table...

Question

Hi,

I need to be able to return a list of all children given a parent Id at all levels using SQL.

The table looks something like this:

ID   ParentId   Name
---------------------------------------
1    null       Root
2    1          Child of Root
3    2          Child of Child of Root

Give an Id of '1', how would I return the entire list...? There is no limitation on the depth of the nesting either...

Thanks,
Kieron

Answer 1

To get all children for a given @ParentId stored in that manner you could use a recursive CTE.

declare @ParentId int
--set @ParentId = 1

;WITH T AS
(
select 1 AS ID,null AS ParentId, 'Root' as [Name] union all
select 2,1,'Child of Root' union all
select 3,2,'Child of Child of Root'
),
cte AS
(
SELECT ID, ParentId, Name
FROM T 
WHERE ParentId = @ParentId  OR (ParentId IS NULL AND @ParentId IS NULL)
UNION ALL
SELECT T.ID, T.ParentId, T.Name
FROM T 
JOIN cte c ON c.ID = T.ParentId
)
SELECT ID, ParentId, Name
FROM cte
OPTION (MAXRECURSION 0)