Mahmud Adam Mahmud Adam - 4 days ago 5
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

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

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

and

relationship

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