Is there a better approach?

Question

Post reply

Is there a better approach?

my-kah

Newbie

Points: 3
More actions
April 14, 2008 at 10:35 am

#189101

I have a table that references itself and I am trying to pull all the information that relates to the row even if it is twice or three times removed.
example: table FamilyMember contains member_id and member_parentid, I would like to pull all the FamilyMembers that are related to Uncle Bob which would include his great grandchildren.
Right now I am iterating through the table and populating a tmp table. Tried using EXISTS but it only points out the parents. Maybe it's a db design flaw and another table should be insert to handle many-to-many....don't know.
Give your advice.
Thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

- one problem is that most of the time there will be two parents :blink:

some kind of biology or tube issue.. 😉

- build a "parent2Child" table having a double fk relationship to your persons table. Maybe there are even other columns that may be related to the relationship.

Index both fk-columns.

Then you may want to have a look at "recursive cte" overhere at SSC.

http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/

It may be a good start.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

my-kah Newbie Points: 3 More actions · Answer 2

Thank you the recursive cte is what I was looking for and will hopefully be able to obtain all members of the family.

GSquared SSC Guru Points: 260824 More actions · Answer 3

ALZ, why do it as a separate table? Two parents, yes. But why a separate table?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 4

Why materialize it in two tables ?

- I prefer small objects

- Adaptability.

Maybe there are even other columns that may be related to the relationship.