Hello everybdoy,

today I want to write some notes about hierarchy research in ms sql database. 

Imagine you have following structure in your table 

TableWithParentChild:

id               |           rootID   |     some additional fields

1                |            1          |      ----

2                |            1          |      ----

3                |            2          |      ----

4                |            2          |      ----

5                |            2          |      ----

and image that you need to get info like who is below 1. 

for this purpose following sql can be useful:

-- wrap up tree selection into tvf
create function HierarchyFunction(@parentID int)
returns table
as
return
with tree
as
(select id,  rootID from TableWithParentChild
         where id = @parentID
         union all
         select p.id, p.rootID from TableWithParentChild as P
         join tree on tree.id = P.rootID
         and P.id != P.rootID
)
select * from tree

select p.* from  TableWithParentChild as p
join HierarchyFunction(1) as tree
on p.id = tree.id