Get all linked IDs of the same table for a given IDs (CTE)

Question

There is a table with the following structure (simplified):

RowID FK_RowID
1     3
2     1
3     11
4     2
5     4
6     1
7     8
8     9
9
10
11

What I want to have is that i get a list of all linked RowIDs and there linked RowIDs as well.

For example I want all linked RowIDs for RowID 1, I expect the following list:

2
3
4
5
6
11

I tried the follwing CTE but I only get 2,3,6,11:

;WITH CTE 
       AS (
            SELECT RowID, FK_RowID, 1 AS Depth
            FROM tbl
            WHERE RowID = 1
            UNION ALL
            SELECT e.RowID, e.FK_RowID, CTE.Depth + 1 AS Depth
                    FROM CTE
                    INNER JOIN tbl AS e
                    ON e.RowID = CTE.FK_RowID
            WHERE CTE.Depth < 50
          )

SELECT DISTINCT RowID
FROM CTE
WHERE RowID <> 1
UNION 
SELECT RowID FROM tbl WHERE FK_RowID = 1

It is only getting the direct path 1 --> 3 --> 11 but I need the other paths too: 2 --> 4 --> 5

Any ideas?

Answer 1

You need to traverse the hierarchy in both directions:

DECLARE @ID int  = 1;

WITH VTE AS(
    SELECT *
    FROM (VALUES(1,3),
                (2,1),
                (3,11),
                (4,2),
                (5,4),
                (6,1),
                (7,8),
                (8,9),
                (9,NULL),
                (10,NULL),
                (11,NULL))V(RowID,FK_RowID)),
Children AS(
    SELECT V.RowID,
           V.FK_RowID
    FROM VTE V
    WHERE V.RowID = @ID
    UNION ALL
    SELECT V.RowID,
           V.FK_RowID
    FROM Children C
         JOIN VTE V ON C.RowID = V.FK_RowID),
Parents AS(
    SELECT V.RowID,
           V.FK_RowID
    FROM VTE V
    WHERE V.RowID = @ID
    UNION ALL
    SELECT V.RowID,
           V.FK_RowID
    FROM Parents P
         JOIN VTE V ON P.FK_RowID = V.RowID)
SELECT RowID
FROM Children
WHERE RowID != @ID
UNION ALL
SELECT RowID
FROM Parents
WHERE RowID != @ID;