CODEWITHSUNDEEP
sqlsql-servert-sqlrecursion
arth81
arth81

Reputation: 229

How to display parent id for itself and children with T-sql recursive query

I am working on recursive query which take table with parent-child relation

ID | ParentID | description
1 | null | Company
2 | 1 | Department
3 | 2 | Unit1
4 | 2 | Unit2
5 | 4 | Unit3
6 | 4 | Unit4

and is suppose to display following result:

ID | ParentID | description
1 | null | Company
2 | 2 | Department
3 | 2 | Unit1
4 | 2 | Unit2
5 | 2 | Unit3
6 | 2 | Unit4

Of course the number of Deparments and units is larger. The basic quest is to display parentId for parent and its child level. Do you have any ideas how to achive this?

So far I only made this query

WITH cte (ID, ParentID, description)
AS
(
    SELECT ID, ParentID, description
    FROM T1
  UNION ALL
    SELECT  e.ID, e.ParentID, e.description
    FROM T2 AS e
    JOIN cte ON e.ID = cte.ParentID

)
 SELECT 
 cte.ID, cte.ParentID, cte.description
FROM cte
cte.ParentID is not null

Upvotes: 2

Views: 2063

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Your syntax isn't quite right, but the idea is in the right direction. In the end, you want to fetch the rows where the parent's parent is NULL. This might work (it is untested):

WITH cte(ID, ParentID, description, lev) AS
      (SELECT ID, ParentID, description, 1 as lev
       FROM table T1
       UNION ALL
       SELECT  cte.ID, e.ParentID, cte.description, cte.lev + 1
       FROM table e JOIN 
            cte
            ON e.ID = cte.ParentID
     )
SELECT cte.ID, cte.ParentID, cte.description
FROM cte left outer join
     table t
     on cte.ParentId = t.ParentId     
WHERE t.ParentID is null;

Upvotes: 1

Related Questions