Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mickybee
Starting Member
29 Posts |
 Posted - 2006-05-21 : 17:24:01
|
| Hi all,Im really confused and need some help please from some of you clever people.I know the basics of SQL but Ive no idea how to write a query to do this.Basically, we have two tables, the first containing a list of people and the second containing two relationships to the person table. The first is the parent relationship and the second the child relationship. Each person may be related to one or more other people (e.g. father, son, brother, sister etc.). If you can imagine we have a few hundred people in the person going back a couple of hundred years.Now Ive set the scene Im really trying to create a query that can navigate all the relationships from a set point (Person).Ultimately I would like to (given a PersonID, navigate the full PERSON_ASSOCIATION table to find:a. All people below the PersonID (if there are any)b. All people above the PersonID (if there are any)For example, in the data below there is somebody called John Smith.Going down the PERSON_ASSOCIATION table I would like to show:John Smith has a brother called Andrew Jones who has a brother-in-law called Stephen BarnesJohn Smith has a wife called Jane WallaceJohn Smith has a daughter called Mary JosephWhat I am going round in circles on is the multiple relatationships, e.g. A is related to B who in turn is related to D who in turn is related to E therefore A is related to E.PERSONID NAME 1 John Smith2 Andrew Jones3 Jane Wallace4 Steven Barnes5 Mary Joseph 98 Me99 JoannePERSON_ASSOCIATIONID PARENT_PERSON_ID CHILD_PERSON_ID RELATIONSHIP1 1 2 Brother2 1 3 Wife3 1 5 Daughter4 2 4 Brother-In-Law5 98 1 Father6 98 99 HusbandCan somebody please point me in the right direction by explaining how I can write some SQL that will navigate (and iterate through) all the PERSON and PERSON_ASSOCIATION tables.Im sure there must be a straight forward way of doing this.One final thing, if it helps, Im hoping to eventually end up with a stored procedure which I can basically pass in a PERSON_ID and it returns a list of all the relationships from that person.MANY BIG thanks in advanceIm really sorry but it is driving me crazyMike |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-21 : 19:09:27
|
| Do a search for trees and hierarchies.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Recursive Relationship Query