Mahmud Adam Mahmud Adam - 9 months ago 51
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)?

Answer Source

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