Unable to find the matching child record using recursive CTE

Question

I have the following table @t:

ParentId   SkuName  ChildId
   P1         X1      C1
   C1         X2      C2
   C2         X3      C2

If I pass the ParentId = P1, the desired output is x3

i.e. the stopping condition is the last row matching record and get the sku name for that

row. If no row matched, then return null

My attempt (no working though)

DECLARE @t TABLE (ParentId Varchar(100), Name VARCHAR(20), ChildId Varchar(100))
INSERT INTO @t(ParentId, Name, ChildId)
  SELECT 'P1', 'X1', 'C1' UNION ALL
  SELECT 'C1', 'X2', 'C2' UNION ALL
  SELECT 'C2', 'X3', 'C2' 

Declare @ParentId Varchar(10) = 'P1'

;With CTE As
(
    Select 
        Rn = ROW_NUMBER() Over(Order By (Select 1))
        ,ParentId
        , Name
        , ChildId
    From @t Where ParentId = @ParentId

    Union All

    Select 
        Rn + 1
        ,pc.ParentId as Parents     
        ,pc.Name
        ,pc.ChildId
    FROM @t pc
    JOIN CTE gp on pc.Childid = gp.Parentid
)

Select *
From CTE

Please help

Kevin Aenmey · Accepted Answer · 2012-06-26T11:40:09.150

The problem is with the JOIN in your second UNION query. You are joining on pc.Childid = gp.Parentid. You should be joining on pc.ParentId = gp.Childid.

Also, since your data has both the child and the parent as the same value, you will end up with infinite recursion unless you specify that the recursion should stop when the ParentId equals the Childid. (WHERE gp.ParentId <> gp.Childid)

Is this the result you are looking for?

DECLARE @t TABLE (ParentId VARCHAR(100), Name VARCHAR(20), ChildId VARCHAR(100))
INSERT INTO @t(ParentId, Name, ChildId)
SELECT 'P1', 'X1', 'C1' UNION ALL
SELECT 'C1', 'X2', 'C2' UNION ALL
SELECT 'C2', 'X3', 'C2' 

DECLARE @ParentId VARCHAR(10) = 'P1'

;With CTE As
(
    SELECT 
        Rn = 1
        ,ParentId
        ,Name
        ,ChildId
    FROM @t WHERE ParentId = @ParentId

    UNION All

    SELECT 
        Rn + 1
        ,pc.ParentId as Parents     
        ,pc.Name
        ,pc.ChildId
    FROM @t pc
    JOIN CTE gp 
        on pc.ParentId = gp.Childid
    WHERE gp.ParentId <> gp.Childid
)

SELECT TOP 1 *
FROM CTE
ORDER BY Rn DESC
OPTION (MAXRECURSION 0);

Unable to find the matching child record using recursive CTE

1 Answers1