HELPPP PLEASSEEE!!!!!!

[stalian4002]

I am writing a program to store family tree data, similar to the

GEDCOM format.  I need help writing a sql query to get all the relatives and

determine the relation (father's mother, brother's wife, etc)  for a

particular user.  Here is my basic table structure:

 

Table Users

-UserName (PK)

-FirstName

-AsChildInFamilyID

-AsSpouseInFamilyID

-AsParentInFamilyID

 

Table Families

- FamilyID (PK)

- MarriageDate

 

Table 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 as

well 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 too

not very accurate, because for instance the husband is actually the father to

the child, not the husband), but I need to "update" the Relation field to

include the depth of the traversal.

 

Based on my table structure, a family unit is husband, wife and

children....I somehow have to recursively find all the ascendants,

descendants, etc...

 

Any help on how I can do this would be great. I am using mysql

[fenway]

I just answered another post w.r.t. storing hierarchical data... using "parentID" fields means that it's impossible to do anything recursively without an SP.