Find topmost parents in self-referential table

2 issues with the code:

You need to track your starting ID through the recursion;
The where condition in the recursive part is actually preventing you from getting the result.

As such:

WITH cte AS (
    SELECT ID, link, ID as [StartID]
    FROM @t
    WHERE ID IN (6, 7)
    UNION ALL
    SELECT t.ID, t.link, c.StartID
    FROM @t t
    JOIN cte c ON t.ID = c.link
)
SELECT c.StartID, c.ID
FROM cte c
where c.link is null;

Tags:

Sql

Sql Server

Related