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

Regular Bill Karwin created some nice explanation of hierchical data & how to use the different solutions: slideshare.net/billkarwin/models-for-hierarchical-data — Wrikken
– Wrikken, Commented Jul 18, 2010 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 dev.mysql.com/tech-resources/articles/hierarchical-data.html an option? Or can we assume some maximum depth? Also see this related question stackoverflow.com/questions/169817/… — Martin Smith
– Martin Smith, Commented Jul 18, 2010 at 15:59

Daniel Vassallo · Accepted Answer · 2010-07-18 19:00:29Z

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

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.

edited Jul 18, 2010 at 19:00

answered Jul 18, 2010 at 18:55

Daniel Vassallo

346k72 gold badges514 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iddqd Over a year ago

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.

Daniel Vassallo Over a year ago

@iddqd: Yes, that's doable. But as you are seeing, that is the limitation of this model.

Collectives™ on Stack Overflow

recursive self query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related