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