CODEWITHSUNDEEP
sqlsql-server-2008
Igor
Igor

Reputation: 281

Query for Parent Child Relationship to the top level

NodeId    NodeName    ParentId   Permission
-----------------------------------------------
1         Node1       0          1
2         Node2       1          NULL
3         Node3       1          NULL
4         Node4       1          NULL
5         Node5       2          NULL
6         Node6       5          NULL
7         Node7       2          NULL

I am in Node6 and I need to get the first not NULL permission in the tree (6->5->2->1-> Permission = 1) How can I do it?

Upvotes: 0

Views: 633

Answers (1)

gbn
gbn

Reputation: 432667

Because Permission is NOT NULL must be in the hierarchy, then this row becomes the root node for this case. I've labelled this RootID here

Also added more sample data to show multiple branches from the same ParentID = 0 root

DECLARE @t TABLE (NodeId int, NodeName varchar(100), ParentId int, Permission int)
INSERT @t VALUES 
(1,'Node1',0,NULL),
(2,'Node2',1,1),
(3,'Node3',1,NULL),
(4,'Node4',1,NULL),
(5,'Node5',2,NULL),
(6,'Node6',5,NULL),
(7,'Node7',2,NULL),
(8,'Node1',0,NULL),
(9,'Node9',8,2),
(10,'Node10',9,NULL),
(11,'Node11',10,NULL),
(12,'Node12',11,NULL),
(13,'Node13',10,NULL),
(14,'Node14',9,NULL);

WITH CTE AS
(
    SELECT NodeId, NodeName, ParentId AS RootID FROM @t WHERE Permission IS NOT NULL
    UNION ALL
    SELECT T.NodeId, T.NodeName, CTE.RootID
    FROM @t T JOIN CTE ON T.ParentId = CTE.NodeId
)
SELECT
    *
FROM
    CTE
WHERE
    NodeName IN ('Node6', 'Node13');

Upvotes: 1

Related Questions