How do I make multiple self joins in mysql?

I am working on a genealogy database. In simple terms a single table called ancestors, consists of records with the following structure:

 name_id | name | mother_id | father_id
---------+------+-----------+----------- 1 | John | 2 | 3 2 | Mary | 4 | 5 3 | Dave | 6 | 7

The first query finds parent ids for John: eg John has parents with ids 2 and 3

Then two further queries find the parent names for the parent ids: Parent id 2 has the name Mary, and Parent id 3 has the name Dave

Three queries have been used to find that: John has parents called Mary and Dave.

Can this be done with a single query, and will there be any gain in performance?

3 Answers

SELECT me.name, mother.name, father.name FROM ancestors me JOIN ancestors mother ON me.mother_id = mother.name_id JOIN ancestors father ON me.father_id = father.name_id WHERE ancestors.id = 1
;

And yes, it's generally faster to run the above than to run three separate lookup queries.

Join as you usually would, but simply use the same table each time, giving each join a unique alias:

SELECT people.name_id, people.name, mothers.name_id, mothers.name, ...
FROM people
LEFT JOIN people AS mothers ON people.mother_id = mothers.name_id
LEFT JOIN people AS fathers ON people.father_id = fathers.name_id
etc...

Have a look at graph noSQL databases. They are the perfect fit for what you plan to do: