Recursive CTE tree Up and Down

Question

Hello,

I have a table with two columns id that creates a hierarchy.

The hierarchy is not a classic tree like manager/employee tree, because in my scenario, a child can have more then one parent and vise versa (As you can see in the following image)

I want to create a table function that gets an ID parameter and returns two columns, one is the ID parameter and second is all the related ID's for all the levels, no meter if it is a parent ID or a child ID until all the Id's that are related to each other have been returned.

(of course in the image It shows only one tree, in my real table I have a lot of trees)

This is my query I created, it almost get the job, the problem is that its not go up all the levels, for example if the Root Id is 11, I will not get id's 24 and 35.

alter   function [dbo].[fn_Tree](  
 @ID int  
 )  
returns table  
as  
      
  
 return(  
  
 with cte   
 as  
 (  
  
 select   
 1 as Lvl  
 ,t.Parent   
  ,t.Child  
 from   
 Tbl as t   
 where  
 t.Parent = @ID  
  
  
 union all  
  
  
 select   
 c.Lvl + 1 as Lvl  
 ,t.Parent  
 ,t.Child   
 from  
 cte as c   
 inner join Tbl as t  
 on c.Child = t.Parent   
  
 ),  
 cte2 as (  
  
 select   
 c.Lvl + 1 as Lvl  
 ,t.Parent  
 ,t.Child   
 from  
 cte as c   
 inner join Tbl as t  
 on   
 (  
 c.Child = t.Child   
 and c.Parent <> t.Parent  
 )  
 )  
  
 select   
 c.Lvl  
 ,c.Parent   
 ,c.Child   
 from  
 cte as c  
  
 union all  
  
 select   
 c.Lvl  
 ,c.Parent  
 ,c.Child  
 from  
 cte2 as c  
  
 )  

I prefer not to use a loop because of performance reasons (my end goal is to join this function)

Answer 1

Hi @nd0911

If I understand correctly, you can try this query.

create or alter function [dbo].[fn_Tree](@ID int)  
returns table  
as  
return(  
  with t1 as(  
    select child from tbl where parent = @id  
    union all  
    select b.child from t1 as a inner join tbl as b  
    on b.parent = a.child   
  ),t2 as(  
    select child from t1   
    union all  
    select d.parent from t2 as c inner join tbl as d  
    on d.child = c.child   
  ),t3 as(  
    select child from t2  
    union all  
    select f.child from t3 as e inner join tbl as f  
    on f.parent = e.child  
  ),t4 as(  
    select parent from tbl where child = @id  
    union all  
    select h.parent from t4 as g inner join tbl as h  
    on h.child = g.parent   
  ),t5 as(  
    select parent from t4  
    union all  
    select j.child from t5 as i inner join tbl as j  
    on j.parent = i.parent   
  ),t6 as(  
    select parent from t5  
    union all  
    select l.parent from t6 as k inner join tbl as l  
    on l.child = k.parent  
  ),t7 as(  
    select parent from t6  
    union all  
    select n.child from t7 as m inner join tbl as n  
    on n.parent = m.parent)  
  select @id as Root,child as 'Relateds Ids' from t3   
  union  
  select @id,parent from t7)  

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

you just join part to child and child to parent

declare @id int = 14;  
with p1 as  
(  
	select parent, child  
	from parentchild   
	where parent = @id  
	union all  
	select p2.parent, p2.child  
	from parentchild p2    
	inner join p1 on p1.child = p2.parent  
 ), p3 as  
 (  
	select child, parent  
	from parentchild   
	where child = @id  
	union all  
	select p4.child, p4.parent  
	from parentchild p4   
	inner join p3 on p3.parent = p4.child  
 )  
 select @id, child  
 from p1   
 union  
 select @id, parent  
 from p3;  
  

Answer 3

can someone please tell me why I cant publish a message ?

I tried to publish 10 times the values for my second scenario and I get a black screen

Answer 4

Hi @nd0911

I have to say that the code I wrote has a certain limitation, that is, it cannot be recursed indefinitely. For the first case you raised, I wrote yesterday's string of code, and the number of recursions in it was enough. But the relationship in your second case is significantly more complicated, and I can only increase the number of recursions in the code to achieve the result you want.
For your second case, you can try the following code.

create or alter function [dbo].[fn_Tree](@ID int)  
returns table  
as  
return(  
  with t1 as(  
    select child from tbl where parent = @id  
    union all  
    select b.child from t1 as a inner join tbl as b  
    on b.parent = a.child   
  ),t2 as(  
    select child from t1   
    union all  
    select d.parent from t2 as c inner join tbl as d  
    on d.child = c.child   
  ),t3 as(  
    select child from t2  
    union all  
    select f.child from t3 as e inner join tbl as f  
    on f.parent = e.child  
  ),t4 as(  
    select child from t3   
    union all  
    select h.parent from t4 as g inner join tbl as h  
    on h.child = g.child   
  ),t5 as(  
    select child from t4  
    union all  
    select j.child from t5 as i inner join tbl as j  
    on j.parent = i.child  
  ),t6 as(  
    select parent from tbl where child = @id  
    union all  
    select l.parent from t6 as k inner join tbl as l  
    on l.child = k.parent   
  ),t7 as(  
    select parent from t6  
    union all  
    select n.child from t7 as m inner join tbl as n  
    on n.parent = m.parent   
  ),t8 as(  
    select parent from t7  
    union all  
    select p.parent from t8 as o inner join tbl as p  
    on p.child = o.parent  
  ),t9 as(  
    select parent from t8  
    union all  
    select r.child from t9 as q inner join tbl as r  
    on r.parent = q.parent  
  ),t10 as(  
    select parent from t9  
    union all  
    select t.parent from t10 as s inner join tbl as t  
    on t.child = s.parent)  
  select @id as Root,child as 'Relateds Ids' from t5   
  union  
  select @id,parent from t10);  

Best regards,
Percy Tang