Parent Child Relationship in SQL Server 2019

Question

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.

SQL 2029? Where did you get your time machine from or where can I buy one? — Thom A, Feb 11 '22 at 15:38
Read about recursive CTEs: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15#d-using-a-recursive-common-table-expression-to-display-multiple-levels-of-recursion — David Browne - Microsoft, Feb 11 '22 at 15:40
Normally in a hierarchy you store either the parent or the child, not both. The above implies that a parent can only have 1 child and a child can only have 1 parent? — Thom A, Feb 11 '22 at 15:41
SQL Server has a `hierarchyid` type that allows representing hierarchies *without* a Parent/Child relation. The performance is *several* orders of magnitude faster, especially in deep trees. That type is essentially a path along the hierarchy, eg `1/12/125` so searching eg for all children of a row is equivalent to searching for all hierarchyid values that start with the row's hierarchy path, eg `1/12/*`. That's a simple range search that can take advantage of indexes. Finding the level only needs to read that path value and count the parts. That's already available as a function — Panagiotis Kanavos, Feb 11 '22 at 15:42
@larnu LOL, I meant 2019. And yes, a parent can have multiple children. Note that I can populate the table in other ways to make for easier selection. — Jason R., Feb 11 '22 at 15:44
Consider using a [hierarchyid](https://learn.microsoft.com/en-us/sql/relational-databases/tables/tutorial-using-the-hierarchyid-data-type?view=sql-server-ver15) instead — Panagiotis Kanavos, Feb 11 '22 at 15:46
Are you sure that you don't have an cycle (or loop) in your data, e.g. Alice > Bob > Cynthia > Doug > Alice > Bob > ... ? [This](https://stackoverflow.com/a/42139978/92546) answer demonstrates one way of terminating recursion in the presence of cycles. — HABO, Feb 11 '22 at 17:00

Debjyoti Saha · Accepted Answer · 2022-02-11T16:49:10.247

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.

score 0 · Answer 2 · 2022-02-11T16:12:39.530

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;
``

Parent Child Relationship in SQL Server 2019

2 Answers2