SQL Sever parent, child, child relationships within table

Question

I have the following table of items with contain a list of name with map back to a parent field within the table -

id  nameVal parentId  
1   A   NULL  
2   B   NULL  
3   C   NULL  
4   D   NULL  
5   E   NULL  
6   A1  1  
7   A2  6  
8   A3  1  
9   A4  7  
10  B1  2  
11  B2  2  

it can be more that one step away from the parent record - A, A1, A4 are all related etc. as below...

A1 => A
A2 => A1 => A
A3 => A
A4 => A2 => A1 => A

So what I'm trying to do is pull through all records where a relationship exists, ie, A4 would bring back all the A's as there is a link to the original A records.

Is this possible?

Answer 1

You can do this with a recursive CTE. The key is to get the parent. Here is one method:

with cte as (
      select id, nameval, id as orig
      from t
      where parentid is null
      union all
      select t.nameval, cte.orig
      from cte join
           t
           on t.parentid = cte.id
    )
select cte.*
from cte
where cte.orig = (select cte2.orig from cte cte2 where ct2.nameval = 'A4');

Answer 2

Perhaps a little more than necessary, but consider the following:

You can set the top node (null will default to the entire hierachy)

You also can set a filter. This can be empty for no filter, a single ID, or a delimited string of IDs.

Declare @T table (id int,nameVal varchar(50),parentId int)
Insert into @T values 
(1   ,'A',   NULL),  
(2   ,'B',   NULL),  
(3   ,'C',   NULL),
(4   ,'D',   NULL),  
(5   ,'E',   NULL),  
(6   ,'A1',  1),  
(7   ,'A2',  6),  
(8   ,'A3',  1),  
(9   ,'A4',  7),  
(10  ,'B1',  2),  
(11  ,'B2',  2)  

Declare @Top    int         = null      --<<  Sets top of Hier Try 6
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability
Declare @Filter varchar(25) = '7'       --<<  Empty for All or try '7,10'

;with cteP as (
      Select Seq  = cast(10000+Row_Number() over (Order by nameVal) as varchar(500))
            ,ID
            ,parentId
            ,Lvl=1
            ,nameVal 
      From   @T 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(parentId,-1) else ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.nameVal)) as varchar(500))
            ,r.ID
            ,r.parentId
            ,p.Lvl+1
            ,r.nameVal
      From   @T r
      Join   cteP p on r.parentId = p.ID)
     ,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP)
     ,cteR2 as (Select A.Seq,A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
     ,cte   as (
                Select A.R1  
                      ,B.R2
                      ,A.ID
                      ,A.parentId
                      ,A.Lvl
                      ,nameVal = Replicate(@Nest,A.Lvl-1) + A.nameVal
                 From cteR1 A
                 Join cteR2 B on A.ID=B.ID
               )
Select Distinct A.*
 From  cte A
 Join (
        Select A.R1,A.R2 
         From  cte A
         Join (Select R1 from cte Where IIF(@Filter='',1,0)+CharIndex(concat(',',ID,','),concat(',',@Filter+','))>0) B
           on B.R1 between A.R1 and A.R2
      ) B on A.R1 between B.R1 and B.R2
 Order By A.R1

Returns

Now, if you set @Filter = '7,10', you'll get

If you set @Filter = '', you'll get

Answer 3

You can use a recursive query to fetch all of the related rows. Whether the data will be in the form you want, I don't know, as the question seems a bit unclear about that. But for example

Fetch a record and all descendants:

with r as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join r
                    on t1.parent_id = r.id
)
select * from r;

Fetch a record and all ancestors:

with r as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join r
                    on t1.id = r.parent_id
)
select * from r;

Now maybe you want both children and ancestors. This can get a little trickier; recursion works best in a straight line so there can be no infinite loops. One way is to union together the two above queries. If your real query has complex logic that you don't want to write twice, then you could use this to get a list of ID's and then run the real query over a select ... where id in (my_list) type query.

Another consideration is whether a record can have multiple children. If we have

A
A1 => A
A10 => A1
A11 => A1
A2 => A
A20 => A2
A21 => A2

you could say these are all related (through A; some are "cousins"). So if you search from A1 and union the first two example queries, you'd get A, A1, A10, A11... but would you also want the other children of A? If so you could take a slightly different approach:

First, find the eldest ancestor:

with r as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join r
                    on t1.id = r.parent_id
)
select id from r where parent_id is null;

Then run the original "all descendants" query against that ID. If you want to get it all into a single statement, the following *should * work I think (but I'm not where I can test it):

with ancestors as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join ancestors
                    on t1.id = ancestors.parent_id
) , related as (
     select *
       from ancestors
      where parent_id is null
     union all
     select t1.*
       from            my_table t1
            inner join related
                    on t1.parent_id = related.id
)
select * from related;