# 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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by