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
;