Sql-server – Select all children down a hierarchy

recursivesql serversql-server-2005

I have a table of hierarchical data like this:

BookGroupID  GroupName  ParentGroupID
-----------  ---------  -------------
0            Primary    0
1            abc        0
2            abd        0
3            abe        0
4            cbc        1
5            ceg        2
6            cjd        3
7            hjd        5
8            bjy        5
9            mfs        4
10           ikl        7
11           hjy        8
12           mnf        5
13           aws        10
14           qws        11
15           aqe        13

I want to select a specific BookGroupID and all its direct and indirect children down the hierarchy. For instance, for the BookGroupID of 2 the output should look like this:

BookGroupID
-----------
2
5
7
8
10
11
12
13
14
15

To explain, row 5 is in the output because it is a direct child of row 2, rows 7, 8 and 12 are direct children of 5, 15 is a child of 13 which is a child of 10 which is a child of 7, 14 is a child of 11 which is a child of 8.

So, how can I do that in SQL?

Best Answer

The technique to use is called a recursive common table expression, or recursive CTE. The Microsoft SQL documentation is comprehensive and there are a lot of third-party blog posts to help explain.

Here's an implementation for your specific question.

This part just creates the sample data from your question. It uses a temporary table (one that uses the # symbol)

    create table #t (BookGroupID int, GroupName varchar(100), ParentGroupID int)
    insert into #t 
    values 
    (0      ,'Primary',    0    ),
    (1      ,'abc',        0    ),
    (2      ,'abd',        0    ),
    (3      ,'abe',        0    ),
    (4      ,'cbc',        1    ),
    (5      ,'ceg',        2    ),
    (6      ,'cjd',        3    ),
    (7      ,'hjd',        5    ),
    (8      ,'bjy',        5    ),
    (9      ,'mfs',        4    ),
    (10     ,'ikl',        7    ),
    (11     ,'hjy',        8    ),
    (12     ,'mnf',        5    ),
    (13     ,'aws',        10),
    (14     ,'qws',        11),
    (15     ,'aqe',        13);

This is the value from which to start looking:

    declare @parent int = 2;

This is the recursive CTE. You do not have to call it "cte" as in this example. The initial semicolon is redundant. It ensures the previous statement is properly ended if the programmer has been lazy. It does no harm to leave it.

    ;with cte 
    as (select BookGroupId
        from #t as t 
        where BookGroupId = @parent
        UNION ALL
        select t.BookGroupId
        from #t as t 
        join cte 
        on t.ParentGroupId = cte.BookGroupId)
    select *
    from cte
    order by BookGroupId;
Related Question