How to traverse through a table which has relation

Question

I have a table like the following

Node_Id     Node_Name     Parent_Node_Id
-------     ---------     --------------
1           Root          0
2           Node1         1
3           Node2         1
4           Node3         2
5           Node4         2
6           Node5         5
7           Node6         5
8           Node7         7

Each row of the table has a parent row which is indicated by the "Parent_Node_Id" and each row/node can be parent of any number of row/nodes.

How can I write a query which will give me the list of all nodes who have a parent node who all share the same parent node? The Parent_Node_Id will be passed as the parameter. For example, if Parent_Node_Id is passed as 2, the query will return the following -

Node_Id     Node_Name     Parent_Node_Id
-------     ---------     --------------
4           Node3         2
5           Node4         2
6           Node5         5
7           Node6         5
8           Node7         7

Answer 1

;WITH CTE AS (
    SELECT Node_Id,Node_Name, Parent_Node_Id 
    FROM @a WHERE Parent_Node_Id = 2
    UNION ALL
    SELECT c.Node_Id,c.Node_Name, c.Parent_Node_Id 
    FROM @a c
    INNER JOIN CTE p ON p.Node_Id = c.Parent_Node_Id
)
Select * from CTE

with example data

Declare @a Table( Node_Id int,    Node_Name varchar(100),     Parent_Node_Id int)
insert into @a 

select 1,'Root',0 
UNION SELECT 2,'Node1',1
UNION SELECT 3,'Node2',1
UNION SELECT 4,'Node3',2
UNION SELECT 5,'Node4',2
UNION SELECT 6,'Node5',5
UNION SELECT 7,'Node6',5
UNION SELECT 8,'Node7',7