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
FROM @Relationship AS r; -- to see what we have...
DECLARE @person VARCHAR(25) = 'Lincoln'
WITH sib ( RelationID, person, Sibling,RreciprocalRelationID ) AS
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 )