9

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
iddqd
  • 700
  • 7
  • 21
  • 4
    Regular Bill Karwin created some nice explanation of hierchical data & how to use the different solutions: http://www.slideshare.net/billkarwin/models-for-hierarchical-data – Wrikken Jul 18 '10 at 15:42
  • MySQL does not have recursive CTEs so I think this would need a cursor if you need to handle an arbitrary depth. Is changing the structure http://dev.mysql.com/tech-resources/articles/hierarchical-data.html an option? Or can we assume some maximum depth? Also see this related question http://stackoverflow.com/questions/169817/is-it-possible-to-query-a-tree-structure-table-in-mysql-in-a-single-query-to-any – Martin Smith Jul 18 '10 at 15:59
  • Bill Karwin's slides are really cool. – iddqd Jul 24 '10 at 11:27

1 Answers1

9

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):

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.

Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • ok, many thanks for the links & comments, I get it now. Since I'm not assuming the trees to be very deep and I just need this rarely, I've decided to simply do two self joins, and if the parentID is still NOT NULL, i run the query again. – iddqd Jul 19 '10 at 08:16
  • @iddqd: Yes, that's doable. But as you are seeing, that is the limitation of this model. – Daniel Vassallo Jul 19 '10 at 08:19