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 |
|
stalian4002
Starting Member
3 Posts |
 Posted - 2009-01-06 : 12:09:24
|
| I am writing a program to store family tree data, some what similar to theGEDCOM format. I need help writing a sql query to get all the relatives anddetermine the relation (father's mother, brother's wife, etc) for aparticular user. Here is my basic table structure:Table Users-UserName (PK)-FirstName-AsChildInFamilyID-AsSpouseInFamilyID-AsParentInFamilyIDTable Families- FamilyID (PK)- MarriageDateTable FamilyMembers- FamilyMemberID (PK)- FamilyID (FK)- UserName- Relation (0 - Husband, 1 - Wife, 3-Child)---> So, given any user, I need to find all the relatives (traverse up aswell as down the tree) and also calculate the relationships as I go along.The relation column stores the relation specific only to the family (that toonot very accurate, because for instance the husband is actually the father tothe child, not the husband), but I need to "update" the Relation field toinclude the depth of the traversal.Based on my table structure, a family unit is husband, wife andchildren....I somehow have to recursively find all the ascendants,descendants, etc...Any help on how I can do this would be great |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 12:12:23
|
| are you using sql 2005? |
 |
|
|
stalian4002
Starting Member
3 Posts |
Posted - 2009-01-06 : 17:23:50
|
| im using mysql |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
HELP PLEASE!!!