Building a hierarchical tree with a single SQL query
It is possible with a single query in Postgres using a recursive common table expression. This is not possible in MySQL as it is one of the few database to not support recursive CTEs.
It would look something like this (not tested)
WITH RECURSIVE tree (id, par, name) AS ( SELECT id, par, name FROM the_table WHERE name = 'Nicole' UNION ALL SELECT id, par, name FROM the_table tt JOIN tree tr ON tr.id = tt.par)SELECT *FROM tree
For Postgres, see http://www.postgresql.org/docs/8.4/static/queries-with.html
MySQL doesn't support this syntax (unless it's in a beta/development tree somewhere). Oracle has something similar using connect by prior
.
This article is probably what you need to look at:
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/