How to all possible child rows in a SQL table when using id parentid relationship in single table

Question

I have the following table schema:

ID | PARENT ID | NAME
-------------------------
1  | NULL      | A - ROOT
2  | 1         | B
3  | 2         | C
4  | 1         | D
5  | 4         | E
6  | 5         | F

The hierarchy look like:

   A
   -- B
   -- -- C
   -- D
   -- -- E
   -- -- -- F

I want to get all child recursively in all descendant levels.

For example when I have A and query for it, I would like to get back A, B, C, D, E and F.

When I have E I want to get E and F.

When I have D I want to get D, E and F.

I am not SQL expert and as a developer normally I would build programmatically loops with DB query and check whether I have children or not and recursively get the children. But this i definitely a very expensive/unperformant approach.

Is there an elegant/better way by using a SQL statement?

Take a look at recursive CTEs( Common Table Expressions). I.E http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — Eduard Uta, Aug 25 '16 at 12:26
Recursive Common Table Expression https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx — Serg, Aug 25 '16 at 12:28
Thanks a lot! I made it work by using CTEs! I never heard before of CTE. I will upvote all comments and solutions which directed me to the right direction. Thank you. — STORM, Aug 25 '16 at 12:56

score 1 · Accepted Answer · answered Aug 25 '16 at 12:53

Here is a generic hierarchy build. This one will maintain presentation sequence and also includes RANGE KEYS (optional and easy to remove if not needed)

Declare @YourTable table (ID int,Parent_ID int,Name varchar(50))
Insert into @YourTable values (1,null,'A - Root'),(2,1,'B'),(3,2,'C'),(4,1,'D'),(5,4,'E'),(6,5,'F')


Declare @Top  int = null          --<<  Sets top of Hier Try 4
Declare @Nest varchar(25) ='   '  --<<  Optional: Added for readability

;with cteHB (Seq,ID,Parent_ID,Lvl,Name) as (
    Select  Seq  = cast(1000+Row_Number() over (Order by Name) as varchar(500))
           ,ID
           ,Parent_ID
           ,Lvl=1
           ,Name 
     From   @YourTable 
     Where  IsNull(@Top,-1) = case when @Top is null then isnull(Parent_ID,-1) else ID end
     Union  All
     Select Seq  = cast(concat(cteHB.Seq,'.',1000+Row_Number() over (Order by cteCD.Name)) as varchar(500))
           ,cteCD.ID
           ,cteCD.Parent_ID,cteHB.Lvl+1
           ,cteCD.Name 
     From   @YourTable cteCD 
     Join   cteHB on cteCD.Parent_ID = cteHB.ID)
    ,cteR1 as (Select Seq,ID,R1=Row_Number() over (Order By Seq) From cteHB)
    ,cteR2 as (Select A.Seq,A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
Select B.R1  
      ,C.R2
      ,A.ID
      ,A.Parent_ID
      ,A.Lvl
      ,Name = Replicate(@Nest,A.Lvl) + A.Name
 From cteHB A
 Join cteR1 B on A.ID=B.ID
 Join cteR2 C on A.ID=C.ID
 Order By B.R1              --<< Or use A.Seq

Returns

R1  R2  ID  Parent_ID   Lvl    Name
1   6   1   NULL        1      A - Root
2   3   2   1           2         B
3   3   3   2           3            C
4   6   4   1           2         D
5   6   5   4           3            E
6   6   6   5           4               F

score 1 · Answer 2 · answered Aug 25 '16 at 12:53

1

you can use Common table expressions as below:

;with cte as 
(
select id, name from hierarchy where parentid is null

union all

select h.id, h.name from hierarchy h inner join cte c
on h.parentid = c.id
)
select * from cte

answered Aug 25 '16 at 12:53

Kannan Kandasamy

13,405
3
25
38

score 1 · Answer 3 · answered Aug 25 '16 at 12:58

This will give you the results you want:

DECLARE @search nvarchar(1) = 'D'

;WITH cte AS (
SELECT  ID,
        [PARENT ID],
        NAME
FROM YourTable
WHERE NAME = @search
UNION ALL
SELECT  y.ID,
        y.[PARENT ID],
        y.NAME
FROM YourTable y
INNER JOIN cte c
    ON y.[PARENT ID] = c.ID
)

SELECT *
FROM cte
ORDER BY NAME

How to all possible child rows in a SQL table when using id parentid relationship in single table

3 Answers3