In my database I have a family tree table containing names, ids, and genders of family members, and another table that has a parent column and a child column containing ids. I am trying to find the parent-child relationship and I am having some difficulty getting the query right. For example, I want to find the father of 'John', so my query is:
`select name from tree inner join relationship on id=parent where gender='m' and name="John"...`
Seems like you would need two references to the
Without a definition of the tables and example data, we're just guessing.
SELECT p.name FROM tree p JOIN relationship r ON r.parent = p.id JOIN tree c ON c.id = r.child WHERE p.gender = 'm' AND c.name = 'John'
assuming (for example)
tree id name gender ---- ---- ------ 402 John m 399 Adam m
relationship parent child ------ ----- 399 402