How to get the child nodes from a parent node from a table in SQL?

Question

Say I have the following hierarchical representation in my database:

A
|_B_C
|_D

then I want to get the child nodes from A (or B). and vice versa, I want to get the parent from a given child node? <>

CREATE TABLE tbl (
   Node HierarchyID PRIMARY KEY CLUSTERED,
   NodeLevel AS Node.GetLevel(),
   ID INT UNIQUE NOT NULL,
   Name VARCHAR(50) NOT NULL
 )

inserting the root:

INSERT INTO tbl (Node, ID, Name)
   VALUES (HierarchyId::GetRoot(), 1, 'A')

child B

DECLARE @parent HierarchyId = HierarchyId::GetRoot()
INSERT INTO tbl (Node,ID,Name) VALUES (@parent.GetDescendant(NULL,NULL),2,'B')

Table definitions, example data and required results would help. — Laurence, Mar 19 '15 at 23:37

score 0 · Answer 1 · answered Mar 20 '15 at 00:43

select @child = node from tbl where id = 2;
-- get immediate ancestor
select * from dbo.tbl where Node = @child.GetAncestor(1);

-- get immediate children
select
    *
from
    dbo.tbl
where
    Node.IsDescendantOf(@parent) = 1 and 
    Node.GetLevel() = @parent.GetLevel() + 1;

How to get the child nodes from a parent node from a table in SQL?

1 Answers1