Tsql - Recursive CTE - max recursion error

Question

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
;

Answer 1

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

`

Answer 2

Your data has a circular dependency.

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

Answer 3

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.

Answer 4

I suggest you read this:
https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child