Get all children of a parent SQL Server

SQL Server > With > Scripts

Get all children of a parent SQL Server

In a hierarchical structure of tables is necessary to traverse the entire structure to find all the children of a parent.

Example:

create table #user(id int, superiorid int)

insert into #user(id, superiorid) values  (1,null), (2,1) , (3,1)

declare @id int

select @id = 1;

with Parent (superiorid,id)

             as
             (
                    select u.superiorid, u.id
                    from #user u
                    where u.ID = @id
                    union all
                    select u.superiorid ,u.ID
                    from #user u
                    inner join Parent p on p.id = u.superiorid
             )     

Select #user.id, #user.superiorid

from #user
inner join Parent p on p.id = #user.id

drop table #user

Result:

id superiorid
1 NULL
2 1
3 1