today I want to write some notes about hierarchy research in ms sql database.
Imagine you have following structure in your table
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