question

PhilArmstrong-8051 avatar image
0 Votes"
PhilArmstrong-8051 asked PhilArmstrong-8051 commented

Tsql - Recursive CTE - max recursion error

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
;

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What result do you expect?


0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered PhilArmstrong-8051 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks all for your help.
I think this will get me there.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

Your data has a circular dependency.

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @PhilArmstrong-8051,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered PhilArmstrong-8051 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Will do -
Thanks!

0 Votes 0 ·