Find all indirectly related family members in @Family(Family,Person) - recursive query

Hi,Is it possible to find all the families that a person is indirectly related to?

declare @Family  table(
Family varchar(20),
Person varchar(20) 

insert into @Family(Family,Person) values('Fam1','Person1')
insert into @Family(Family,Person) values('Fam1','Person1')
insert into @Family(Family,Person) values('Fam2','Person1')
insert into @Family(Family,Person) values('Fam2','Person2')
insert into @Family(Family,Person) values('Fam2','Person2')
insert into @Family(Family,Person) values('Fam2','Person3')
insert into @Family(Family,Person) values('Fam3','Person2')
insert into @Family(Family,Person) values('Fam3','Person3')
insert into @Family(Family,Person) values('Fam4','Person3')
insert into @Family(Family,Person) values('Fam5','Person3')
insert into @Family(Family,Person) values('Fam5','Person4')
insert into @Family(Family,Person) values('Fam6','Person4')

Open in new window

declare @Person varchar(20)
set @Person = 'Person4'

Person4 --> In Fam6
        --> In Fam5
Fam5   --> has person 3 in it
Person3 --> In Fam4
        --> In Fam3
Fam3    --> has person 2 in it
Person 2 --> in Fam2
Fam2 --> Has person 1 in it
Person 1 --> In Fam1
No further levels

Open in new window

Thus (in)directly Related group of families for Person4


Open in new window

My Horrible Attempt which does not level down enough. I realize that some sort of recursion is required, but need help please.
select Family from @Family where Person in (
select Person from @Family where Family in (
select   Family from @Family where Person = @Person
Group By Family

Open in new window

Received the error
 The maximum recursion 100 has been exhausted before statement completion

Open in new window

from this
WITH  abcd
        AS (
              -- anchor
            SELECT  Family,   Person 
            FROM    @Family
            WHERE   Person = 'Person4'
            UNION ALL
              --recursive member
            SELECT  t.Family,  t.Person
            FROM    @Family AS t
                    JOIN abcd AS a
                      ON t.Person = a.Person 

Open in new window

Who is Participating?
ZberteocConnect With a Mentor Commented:
That happens because you don't have a root in your table to stop the precedence. You need to come up with a structure that a row refers to another row as a parent, one row only one parent, like in a managerial schema:

ID, roll, name, ManagerID
1, CEO, John, NULL -- this is the root, teh CEO of the company
2. CFO, Joe, 1
3. HRM, Anna, 1
4. Accountant, Mark, 2
5. HR clerk, Jane, 3
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.