Tsql - Recursive CTE - max recursion error

Phil Armstrong 21 Reputation points
2021-09-13T18:01:44.537+00:00

Table of related siblings - 3 people - all identified as siblings of each other total of 6 rows.
Seems like recursive CTE should work, but I can't get past the max recursion error.
I would eventually like to turn this into a table valued function, but can't get the script to work...
For the example, I have built the table as a table variable.
Thanks in advance.

DECLARE @Relationship TABLE
(RelationID INT, Person VARCHAR(25), Sibling VARCHAR(25), ReciprocalRelationID INT)

INSERT INTO @Relationship (RelationID, Person, Sibling, ReciprocalRelationID) VALUES

(1,'Lincoln','Aidan', 3),
(2,'Lincoln','Tyler', 5),
(3,'Aidan','Lincoln', 1),
(4,'Aidan','Tyler', 6),
(5,'Tyler','Lincoln', 2),
(6,'Tyler','Aidan', 4)

SELECT *
FROM @Relationship AS r; -- to see what we have...

DECLARE @person VARCHAR(25) = 'Lincoln'
;

WITH sib ( RelationID, person, Sibling,RreciprocalRelationID ) AS
(
SELECT
r.RelationID ,
r.Person ,
r.Sibling ,
r.ReciprocalRelationID
FROM @RELATIONSHIP r
WHERE r.PERSON = @person

UNION ALL

SELECT 
    r2.RelationID ,
    r2.Person ,
    r2.Sibling ,
    r2.ReciprocalRelationID
FROM sib s
JOIN @RELATIONSHIP AS r2
  ON r2.PERSON =  s.sibling
 AND s.RreciprocalRelationID  <> r2.RelationID

)

SELECT *
FROM sib
;

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2021-09-13T21:21:10.33+00:00

    It's not clear to me what result you want, but to avoid the infinite recursion with the reciprocal dependencies, you need to use a materialised path to avoid re-visiting relations. Maybe this can be a start:

    WITH sib ( RelationID, person, Sibling, ReciprocalRelationID, path ) AS (
       SELECT r.RelationID, r.Person, r.Sibling, r.ReciprocalRelationID,
              cast(cast(r.RelationID AS char(10)) as varchar(MAX))
       FROM   @RELATIONSHIP r
       WHERE r.Person = @person
       UNION ALL
       SELECT r2.RelationID, r2.Person, r2.Sibling, r2.ReciprocalRelationID,
              path + cast(r2.RelationID AS char(10)) 
       FROM   sib s
       JOIN   @RELATIONSHIP AS r2 ON r2.Person =  s.Sibling
       WHERE  charindex(cast(r2.RelationID AS char(10)), s.path) = 0  
         AND  charindex(cast(r2.ReciprocalRelationID AS char(10)), s.path) = 0  
    )
    SELECT *
    FROM sib
    

    `

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,721 Reputation points
    2021-09-13T19:04:06.5+00:00

    Your data has a circular dependency.

    RelationId = 1 has a ReciprocalRelationID = 3
    RelationId = 3 has a ReciprocalRelationID = 1

    0 comments No comments

  2. EchoLiu-MSFT 14,581 Reputation points
    2021-09-14T06:12:09.907+00:00

    Hi @Phil Armstrong ,

    As Tom said, your recursive CTE is a wireless loop.Generally speaking, recursive cte can easily handle tree structure or parent-child structure data.

    I'm not sure if your recursive cte is appropriate, because I don't know what result you want.

    I think we should probably start anew from your real needs.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Tom Phillips 17,721 Reputation points
    2021-09-14T16:20:55.533+00:00