Vryjdfkk

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?

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;