# 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')
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

Thus (in)directly Related group of families for Person4

Fam6,Fam5,Fam4,Fam3,Fam2,Fam1

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

The maximum recursion 100 has been exhausted before statement completion

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
)
SELECT * FROM abcd
###### Who is Participating?

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
...
0
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.