Mahmud Adam Mahmud Adam - 7 months ago 43
MySQL Question

Matching parent to child in MySQL query

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"...`

That is not right and I am confused on where to go from here. Basically I want to select the name of the male parent of John, and eventually extend the query out to select John's mother and father. How would I formulate my query to search for John's parents given their names and relationship (via parent and child columns)?


Seems like you would need two references to the tree table.

Without a definition of the tables and example data, we're just guessing.

    FROM tree p
    JOIN relationship r
      ON r.parent =
    JOIN tree c
      ON = r.child
   WHERE p.gender = 'm'
     AND = 'John'

assuming (for example)


id    name  gender
----  ----  ------
 402  John  m
 399  Adam  m



parent  child
------  -----
   399    402