I have the following table below.

I want to select all of the members of Group-D

SELECT * FROM mytable
where grp = "Group-D"

However, Group-D also contains Group-C along with User 5. Group-C contains Group-A and Group-B.

So, the select should return:

User-1
User-2
User-3
User-4
User-5
Group-A
Group-B
Group-C

Obviously, without doing multiple lookups on itself, I will not get that output.

How can I make the select statement do a table lookup within itself like that?

CodePudding user response：

The following recursive CTE will traverse through the data to arrive at parent/child relationships:

declare @value as varchar(40)
set @value = 'Group-D'

;with CTE as (
        select 1 as lvl, @value as member, cast(null as varchar(40)) as parent
        union all                  
        select CTE.lvl   1, t.member, t.grp
        from CTE
        inner join mytable as t on CTE.member = t.grp
        where lvl < 10 
        )
select
    lvl, member, parent
from CTE
order by lvl, member

result

 ----- --------- --------- 
| lvl | member  | parent  |
 ----- --------- --------- 
|   1 | Group-D |         |
|   2 | Group-C | Group-D |
|   2 | User-5  | Group-D |
|   3 | Group-A | Group-C |
|   3 | Group-B | Group-C |
|   4 | User-1  | Group-A |
|   4 | User-2  | Group-A |
|   4 | User-3  | Group-B |
|   4 | User-4  | Group-B |
 ----- --------- --------- 

See this working at db<>fiddle

Note, I introduced "lvl" as a way to implement an "escape" from the recursion - "just in case"

Also, if you don't want to include the top level item in the result, use

select
    lvl, member, parent
from CTE
where parent IS NOT NULL
order by lvl, member

and you could commence lvl as zero instead of 1 in the CTE