CODEWITHSUNDEEP
sql-server-2008t-sql
user1151923
user1151923

Reputation: 1872

How to vertically join the same table

So I have a table like this:

DECLARE @Nodes TABLE (ID INT, Name VARCHAR(100), ParentID INT)
INSERT INTO @Nodes VALUES 
(1, 'Test', NULL),
(2, 'Test', 1),
(3, 'Test', 2),
(5, 'Test', 3),
(6, 'Test', 1)

Now I would like to query that table, retrieving node with ID 3 and all parent nodes. ParentID would be a foreign key to the same table's ID column. The number of possible parent nodes is infinite. Result would be this:

ID  Name    ParentID
1   Test    NULL
2   Test    1
3   Test    2

What is the best way to do this?

Upvotes: 1

Views: 40

Answers (2)

Dony George
Dony George

Reputation: 175

DECLARE @ID INT=3
;with cte as(
select ID,Name,ParentID FROM @Nodes WHERE ID=@ID
UNION ALL
SELECT n.ID,n.Name,n.ParentID FROM cte inner join @Nodes n on cte.ParentID= n.ID
)
SELECT ID,Name,ParentID FROM cte
ORDER BY ParentID

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

You can use a recursive common table expression:

WITH Nodes 
AS(
  SELECT 1 AS relationLevel, child.*
  FROM  @Nodes child
  WHERE child.ID = 3

  UNION ALL

  SELECT relationLevel+1, parent.*
  FROM Nodes nextOne
  INNER JOIN  @Nodes parent ON parent.ID = nextOne.ParentID
)
SELECT * FROM Nodes order by ID

DEMO

Upvotes: 1

Related Questions