Geek Answers Handbook

Sql Server CTE Parental Recursive Child

I have the 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 am trying to build a result set of all parent child DIRECT and INDIRECT relationships . Therefore, suppose that I pass the parameter ParentID = 2, I would like the result set to return exactly as shown below:

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

, , Parent ID = 2. , , Id 6 . 2 1, . , N . , , , , , , .

, , :

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

SQLFiddle , , .

, .

+1
1

: " . 5 6 6 5".

ParentId , .

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

, , - , ChildId null ParentId null. ?

, , ?

+4

More articles:


All Articles