SQL select parent-child recursively based on a reference table

Question

I saw many questions related to a recursive query but couldn't find any that shows how to use it based on a reference table.

I have a MasterTable where Id, ParentId columns are establishing the parent/child relation.

I have a SubTable where I have a bunch of Ids which could be a parent Id or child Id.

I would like to retrieve all related records (parent or child, recursively) from the MasterTable based on given SubTable

Current output:

id          parentId
----------- -----------
1           NULL
2           1
3           1
4           NULL
5           4
6           5
7           6

Expected output

id          parentId
----------- -----------
1           NULL
2           1
3           1
4           NULL
5           4
6           5
7           6
8           9
9           NULL
10          NULL
11          10
13          11
14          10
15          16
16          NULL

Comparison of actual vs expected:

Code:

DECLARE @MasterTable TABLE
(
    id INT NOT NULL,
    parentId INT NULL
);

DECLARE @SubTable TABLE
(
    id INT NOT NULL
);

INSERT INTO @MasterTable (id, parentId)
VALUES (1, NULL), (2, 1), (3, 1), (4, NULL), (5, 4), (6, 5),
       (7, 6), (8, 9), (9, NULL), (10, NULL), (11, 10), (12, NULL),
       (13, 11), (13, 11), (14, 10), (15, 16), (16, NULL);

INSERT INTO @SubTable (id)
VALUES (1), (2), (3), (4), (6), (5), (7),
       (8),  -- it does not show
       (13), -- it does not show
       (15); -- it does not show
/* beside 8,13,15 it should add 9,11,14 and 10,16 */

;WITH cte AS 
(
    SELECT 
        mt1.id,
        mt1.parentId
    FROM 
        @MasterTable AS mt1
    WHERE 
        mt1.parentId IS NULL
        AND EXISTS (SELECT NULL AS empty
                    FROM @SubTable AS st
                    WHERE st.Id = mt1.id)

    UNION ALL

    SELECT 
        mt2.id,
        mt2.parentId
    FROM 
        @MasterTable AS mt2
    INNER JOIN 
        cte AS c1 ON c1.id = mt2.parentId
)
SELECT DISTINCT 
    c2.id,
    c2.parentId
FROM 
    cte AS c2
ORDER BY 
    id;

Answer 1

Is the following query suitable for the issue in question?

with
  r as(
      select
        m.*, iif(m.parentid is null, 1, 0) p_flag
      from @MasterTable m
      join @SubTable s
        on s.id = m.id
    union all
      select
        m.*, iif(m.parentid is null, 1, r.p_flag)
      from r
      join @MasterTable m
        on (r.p_flag = 1 and m.parentid = r.id) or
           (r.p_flag = 0 and r.parentid = m.id)
)
select distinct
  id, parentid
from r
order by id;

Output:

| id | parentid |
+----+----------+
|  1 | NULL     |
|  2 | 1        |
|  3 | 1        |
|  4 | NULL     |
|  5 | 4        |
|  6 | 5        |
|  7 | 6        |
|  8 | 9        |
|  9 | NULL     |
| 10 | NULL     |
| 11 | 10       |
| 13 | 11       |
| 14 | 10       |
| 15 | 16       |
| 16 | NULL     |

Test it online with rextester.com.