我想在层次结构中填充顶层细节。我有两张table。
主表

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

关系表

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

我希望输出如下：

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

我已经使用cte编写了递归sql，但是它没有给出预期的结果。

;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

有人能帮我学sql吗？谢谢。