Building a hierarchical tree with a single SQL query

Question

I have a SQL table with the following structure.

id   - int
par  - int (relational to id)
name - varchar

Column par contains references to id or NULL if no reference, this table is meant to build an hierarchical tree.

Then, given the data:

id par  name

1  NULL John
2  NULL Mario
3  1    George
4  3    Alfred
5  4    Nicole
6  2    Margaret

I want to retrieve a hierarchical tree, up to the last parent, from a given single id.

Example, I want to know the tree from Nicole to the last parent. So the query result will be:

id par  name

5  4    Nicole
4  3    Alfred
3  1    George
1  NULL John

I would normally do this with a SQL query repeating over and over and building the tree server side but I do not want that now.

Is there any way to achieve this with a single SQL query?
I need this for either MySQL or PgSQL.

And I want to know also, if possible, is it also widely supported? In which versions of either MySQL or PgSQL can I expect support?

score 7 · Accepted Answer · answered Aug 31 '11 at 16:02

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

MySQL 8 supports recursive cte, might worth taking a look at this - https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Munish Chandel, Jan 30 '22 at 06:51

score 1 · Answer 2 · edited Aug 31 '11 at 16:02

1

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.

edited Aug 31 '11 at 16:02

answered Aug 31 '11 at 15:59

Marc B

356,200
43
426
500

Benoit · Answer 3 · 2011-08-31T16:09:27.343

0

In Oracle, this is done via:

SELECT [[LEVEL,]] id, par, name FROM my_table
 START WITH name = 'Nicole'
CONNECT BY [[NOCYCLE]] id = PRIOR par
[[ORDER SIBLINGS BY name ASC]]

(my [[…]] syntax denotes optional query bits.

MySQL is planning to integrate such a feature. For PostgreSQL there is another answer helping you.

edited Aug 31 '11 at 16:09

answered Aug 31 '11 at 16:03

Benoit

76,634
23
210
236

Interesting that MySQL is planning that as well. Do you have any reference or timelines for this? – Aug 31 '11 at 16:13
@a_horse_with_no_name: I have probably been fooled by [this page](http://dev.mysql.com/doc/refman/5.0/fr/todo-future.html) because nothing in English is available on the matter. – Benoit Aug 31 '11 at 16:19

Building a hierarchical tree with a single SQL query

3 Answers3

Linked