Fill top level details down hierarchy

Question

I want to fill top level details down the hierarchy. I have two tables.
MainTable

+-----+------+--------+
| ID  | Name | NodeID |
+-----+------+--------+
| 1   | A    | 1      |
+-----+------+--------+
| 2   | B    | 2      |
+-----+------+--------+
| 3   | C    | 3      |
+-----+------+--------+

RelationTable

+----+---------+----------+
| ID | ChildID | ParentID |
+----+---------+----------+
| 1  | 11      | 1        |
+----+---------+----------+
| 2  | 12      | 11       |
+----+---------+----------+
| 3  | 13      | 12       |
+----+---------+----------+
| 4  | 21      | 2        |
+----+---------+----------+
| 5  | 22      | 21       |
+----+---------+----------+

I want the output as follows:

+------+--------+---------+----------+
| Name | NodeID | ChildID | ParentID |
+------+--------+---------+----------+
| A    | 1      | 11      | 1        |
+------+--------+---------+----------+
| A    | 1      | 12      | 11       |
+------+--------+---------+----------+
| A    | 1      | 13      | 12       |
+------+--------+---------+----------+
| B    | 2      | 21      | 2        |
+------+--------+---------+----------+
| B    | 2      | 22      | 21       |
+------+--------+---------+----------+

I have already written the recursive SQL using CTE but it does not give the result as expected.

;WITH relation_cte (childid, parentid) 
     AS (SELECT childid, 
                parentid 
         FROM   @RelationTable AS RT1 
                INNER JOIN @MainTable AS MT 
                        ON MT.nodeid = RT1.parentid 
         UNION ALL 
         SELECT RT2.childid, 
                RT2.parentid 
         FROM   @RelationTable AS RT2 
                INNER JOIN relation_cte AS REL_CTE 
                        ON REL_CTE.childid = RT2.parentid) 
SELECT MT.managername, MT.nodeid, REL_CTE.childid, REL_CTE.parentid 
FROM   relation_cte AS REL_CTE 
       LEFT OUTER JOIN @MainTable AS MT 
                    ON MT.nodeid = REL_CTE.parentid

Can somebody please help me with the SQL? Thanks.

user_546117 · Answer

Add the TopLevelId column to the CTE and don't forget the ORDER BY clause:

;WITH relation_cte (childid, parentid, toplevelid) AS (
    SELECT 
        RT1.childid, 
        RT1.parentid, 
        MT.nodeid 
    FROM   
        @RelationTable AS RT1 
        INNER JOIN @MainTable AS MT ON MT.nodeid = RT1.parentid 
    UNION ALL 
    SELECT 
        RT2.childid, 
        RT2.parentid,
        REL_CTE.toplevelid
    FROM
        @RelationTable AS RT2 
        INNER JOIN relation_cte AS REL_CTE ON REL_CTE.childid = RT2.parentid
) 
SELECT 
    MT.name, REL_CTE.toplevelid, REL_CTE.childid, REL_CTE.parentid 
FROM
    relation_cte AS REL_CTE 
    LEFT OUTER JOIN @MainTable AS MT ON MT.nodeid = REL_CTE.toplevelid
ORDER BY 2

Fill top level details down hierarchy

1 Answers