CODEWITHSUNDEEP
sqlsql-serverrecursioncommon-table-expression
Bat_Programmer
Bat_Programmer

Reputation: 6851

Sql Server CTE Parent Child recursive

I have following table structure:

create table Test(
  ParentId int,
  ChildId int
 )

insert into Test(ParentId, ChildId)
select 1, NULL
union
select 1, 2
union
select 1, 3
union
select 2, NULL
union
select 2, 5
union
select 5, 6
union 
select 6, 5
union 
select 6, 8

I'm trying to build a result set of all parent child DIRECT and INDIRECT relationships. So suppose I pass the parameter of ParentID = 2, I would like the result set to return exactly as below:

ParentId    ChildId
-------------------
2           NULL
2           5
5           6
6           8
1           2

So basically this shows all possible links that can be found in one table for Parent ID = 2. Starting off with Parent itself, it has Child Id which then has other relations with Child Id 6. Also the parent Id 2 falls under parent id 1 which should also show up in result set. Please note the relationship could expand to N number of levels. I hope you understand what I am trying to achieve here, if not then please let me know so that I can explain more clearly.

So far I have come up with below recursive query but it throws an error which is stated below:

DECLARE @ID INT = 2

;WITH MyCTE
AS (
    SELECT ParentId
        ,ChildId
    FROM Test
    WHERE ParentId = @ID

    UNION ALL

    SELECT T.ParentId
        ,Test.ChildId
    FROM Test
    INNER JOIN MyCTE T ON Test.ParentID = T.ChildId
    WHERE Test.ParentID IS NOT NULL
    )
SELECT *
FROM MyCTE

Error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion

I have put up the code on SQLFiddle here for you guys to test and try.

I would really appreciate anyone who guides and helps me in achieving my desired result.

Upvotes: 1

Views: 10305

Answers (1)

GriGrim
GriGrim

Reputation: 2921

As #Mikael Eriksson said: "You have a circular references in your data. 5 is parent to 6 and 6 is parent to 5."

Also in recursive part you output ParentId from previous step, not from just found rows.

declare @Test table (ParentId int, ChildId int)

insert into @Test (ParentId, ChildId)
select 1, null
union all
select 1, 2
union all
select 1, 3
union all
select 2, null
union all
select 2, 5
union all
select 5, 6
union all
--select 6, 5
--union all
select 6, 8

declare @id int = 2

;with MyCTE as (
    select ParentId, ChildId
    from @test
    where ParentId = @id

    union all

    select t2.ParentId, t2.ChildId
    from MyCTE t1
    inner join @Test t2 on t1.ChildId = t2.ParentId
)

select * from MyCTE

Another thing I didn't understand is why do you have such rows where ChildId is null and ParentId is not null. How can it be possible?

Does it mean that you have unknown items whose parent is known?

Upvotes: 4

Related Questions