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'


    SELECT id, par, name 
    FROM the_table tt
      JOIN tree tr ON tr.id = tt.par
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'

(my [[…]] 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: