Query the entire hierarchy based on a parent/child value

Question

Data:

DECLARE @Data TABLE ([parent_id] INT, [child_id] INT) ;

INSERT INTO @Data
SELECT  1, 2
UNION
SELECT  1, 3
UNION
SELECT  2, 3
UNION
SELECT  3, 4

UNION
SELECT  5, 5

UNION
SELECT  6, 6
UNION
SELECT  6, 7

UNION
SELECT  28, 29
UNION
SELECT  28, 30
UNION
SELECT  31, 29
UNION
SELECT  31, 32

Goal: To query the entire tree of the root parent based on a value in that hierarchy.

Scenarios:

If I choose to query for <_id> = 4 (..or 1 ..or 2 ..or 3), the query should return the following.

parent_id   child_id
1           2
1           3
2           3
3           4

if I choose to query for <_id> = 5, the query should return the following.

parent_id   child_id
5           5

If I choose to query for <_id> = 7 (..or 6), the query should return the following.

parent_id   child_id
6           6
6           7

If I choose to query for <_id> = 28, the query should return the following.

parent_id   child_id
28          29
28          30

If I choose to query for <_id> = 29, the query should return the following.

parent_id   child_id
28          29
28          30
31          29
31          32

My attempt: Either doesn't return the ENTIRE hierarchy (@probe_id 4), returns dups (@probe_id = 2), or doesn't return all branches (@probe_id = 29), and etc.

DECLARE @probe_id INT = 4 ;
WITH CTE AS
    (
        SELECT  [child_id]
              , [parent_id]
        FROM    @Data
        WHERE   [parent_id] = @probe_id
                OR [child_id] = @probe_id
        UNION ALL
        SELECT  [T].[child_id]
              , [T].[parent_id]
        FROM    @Data AS [T]
        JOIN    CTE AS [C]
        ON      [T].[parent_id] = [C].[child_id]
    )
SELECT      [parent_id]
          , [child_id]
FROM        CTE
ORDER BY    [parent_id], [child_id]
OPTION ( MAXRECURSION 0 ) ;

Answer 1

Looks like you want first detect the root and then traverse all the tree from the root. Try

declare @id  int = 6;

with h1 as (
   select [parent_id], [child_id], 0 level
   from dd --source table
   where @id in ([parent_id], [child_id])

   union all
  
   select dd.[parent_id], dd.[child_id], level +1
   from h1
   join dd on dd.[child_id] = h1.[parent_id]
      --  prevent short circuit looping 
      and h1.[child_id] <> h1.[parent_id]
), 
root as (
  select top(1) parent_id
  from h1
  order by level desc
),
h2 as (
   select dd.parent_id, dd.[child_id]
   from dd 
   join root on dd.parent_id = root.parent_id

   union all

   select dd.[parent_id], dd.[child_id]
   from h2
   join dd on dd.[parent_id] = h2.[child_id]
      and h2.[child_id] <> h2.[parent_id]
)
select distinct * 
from h2

EDIT

For multi-root hierarchy replace above root expression with

root as (
  select parent_id
  from h1 a
  where not exists (
     select 1 
     from h1 b 
     where a.parent_id = b.child_id 
           -- self-referencing root is ok
           and a.parent_id != b.parent_id )
)

db<>fiddle