I've got a SQL table using the following structure.
id - int par - int (relational to id) name - varchar
Column componen consists of references to id or NULL if no reference, this table is supposed to build an hierarchical tree.
Then, because of the data:
id par name 1 NULL John 2 NULL Mario 3 1 George 4 3 Alfred 5 4 Nicole 6 2 Margaret
I wish to retrieve a hierarchical tree, as much as the final parent, from the given single id.
Example, I wish to be aware of tree from Nicole towards the last parent. Therefore the query result is going to be:
id par name 5 4 Nicole 4 3 Alfred 3 1 George 1 NULL John
I'd normally do that having a SQL query repeating again and again and building the tree server side however i don't want that now.
Can there be in whatever way to do this having a single SQL query?
I want this for either MySQL or PgSQL.
And I wish to know also, if at all possible, could it be also broadly supported? By which versions of either MySQL or PgSQL can one expect support?
It's possible having a single query in Postgres utilizing a recursive common table expression. No chance in MySQL because it is among the couple of database not to support recursive CTEs.
It might look something similar to this (not examined)
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 does not support this syntax (unless of course it's inside a beta/development tree somewhere). Oracle has such like using
connect by prior.
In Oracle, this is accomplished via:
SELECT [[LEVEL,]] id, par, name FROM my_table START WITH name = 'Nicole' CONNECT BY [[NOCYCLE]] id = PRIOR par [[ORDER SIBLINGS BY name ASC]]
[[…]] syntax denotes optional query bits.
MySQL is likely to integrate this type of feature. For PostgreSQL there's another answer assisting you.
This information is most likely what you ought to take a look at: