Parent Child Relationship in SQL Server 2019-CodePudding

I have a table that stores relationships like this:

ParentName ParentID ChildName ChildId
-------------------------------------
Name1      Guid     NameA      Guid
Name2      Guid     NameB      Guid
Name3      Guid     NameC      Guid
NameA      Guid     NameY      Guid
NameB      Guid     NameX      Guid
NameC      Guid     NameZ      Guid
NameY      Guid     Name1A     Guid

My goal is to retrieve data stacked in way that no matter far down the hierarchy, the child columns shows who the top most parent is.

I know it's a recursive CTE, but I don't know how to write the recursive join in the query back to the anchor without breaking the max number of recursion. Parent query has 78 records using a WHERE clause, but total levels shouldn't be more than 4 or 5. The parent can have multiple children.

CodePudding user response：

Here is a working example of this recursive query.

CREATE TABLE parents (
    ParentName char(10),
    ParentID char(10),
    ChildName char(10),
    ChildId char(10) );
insert into parents values( 'a',1,'b',2);
insert into parents values( 'b',2,'c',3);
insert into parents values( 'c',3,'d',4);
insert into parents values( 'd',4,'e',5);
insert into parents values( 'e',5,'f',6);
insert into parents values( 'f',6,'g',7);
insert into parents values( 'g',7,'h',8);

select 
    p1.pname, p1.pid,
    p2.pname, p2.pid,
    p3.pname, p3.pid,
    p4.pname, p4.pid,
    p5.pname, p5.pid,
    p5.cname, p5.cid
from
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p1 right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p2 on p1.cid=p2.pid right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p3 on p2.cid=p3.pid right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p4 on p3.cid=p4.pid right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p5 on p4.cid=p5.pid 
order by
    p1.pid,p2.pid,p3.pid,p4.pid,p5.pid;
DROP TABLE parents;
``

CodePudding user response：

The below query should work. Although it is subjected to maximum recursion permitted by database which is 100 by default in case of SQL server. So it should work till that many level of nested Parent-Child relationship.

WITH RECURSIVE cte AS (
    SELECT ChildID,ChildName,ParentID,ParentName, 1 AS Level FROM parents

    UNION ALL

    SELECT cte.ChildID,
        cte.ChildName,
        GrandParent.ParentID,
        GrandParent.ParentName,
        Level 1 
    FROM cte 
    INNER JOIN parents AS GrandParent 
    ON cte.ParentID=GrandParent.ChildID 
)
SELECT ChildID,ChildName,ParentID,ParentName 
FROM(
    SELECT cte.*,
        ROW_NUMBER() OVER(PARTITION BY ChildID,ChildName ORDER BY Level DESC) AS rnk 
    FROM cte
) Rank_Highest_Level
WHERE rnk=1
ORDER BY ChildID;

Here is a working example in DB Fiddle.