recursive self query

Question

I have the following table:

myTable:
+----+----------+
| id | parentID |
+----+----------+
|  1 |     null |
|  2 |        1 |
|  3 |        1 |
|  4 |        2 |
|  5 |        4 |
-----------------

i would like to get all rows tracing back until there's no parentID anymore. So ".... WHERE id=5" would give me:

5, 4, 2, 1

Answer 1

You are organizing your hierarchical data using the adjacency list model. The fact that such recursive operations are difficult is in fact one major drawback of this model.

Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the WITH keyword.

As for MySQL, you may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier (possible):

• Mike Hillyer: Managing Hierarchical Data in MySQL

In addition, I also suggest checking out Bill Karwin's presentation pointed out in the comments above. The closure table model described is a very valid alternative to the nested set.