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

Fam6,Fam5,Fam4,Fam3,Fam2,Fam1

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 
           )
SELECT * FROM abcd

Open in new window

jxhardingAsked:
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.

ZberteocCommented:
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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.