SQL Server Parent-child Relationship query script

With nodes
As (
SELECT * from users as child where Child.id = 6
UNION ALL
Select par.* from users as par
INNER JOIN nodes as RC on par.id = Rc.pid
)
SELECT * from the users where ID in (
Select ID from Nodes n
)

-- //

With nodes
As (
SELECT * from users as par where par.id = 2
UNION ALL
Select child.* from the users as child
INNER JOIN nodes as RC on child.pid = Rc.id
)
SELECT * from the users where ID in (
Select ID from Nodes n
)

SQL Server Parent-child Relationship query script