MySQL Self-Join

Question

Hi All,

I have a table (this cannot be changed) like the following:

POST_ID | PARENT_ID | POST_NAME

1       | 0         | Services
4       | 1         | Development
5       | 4         | Magento
2       | 0         | Contact

The field, 'parent_id' references post_id to form a self-referential foreign key. Is it possible to write a single query to connect a post and it's parents based on the post_id?

For example, if I had the post_id 5 (Magento), could I write a query to produce the following results:

5 | Magento
4 | Development
1 | Services

I know this is quite easy to do with multiple queries, however, wish to know whether it is possible with a single query.

Thanks :)

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 (also see @Quassnoi's comment below). This feature allows queries such as this to be written with ease, but as @OMG Ponies mentioned in a comment above, MySQL does not support recursive queries yet.

You mentioned that you cannot make any changes to your table, but can you add an additional table? If yes, you may 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 the following presentation by @Bill Karwin, a regular contributor on Stack Overflow:

• Bill Karwin: Models for hierarchical data with SQL and PHP

The closure table model described in the presentation is a very valid alternative to the nested set. He further describes this model in his SQL Antipatterns book (excerpt from the chapter on this topic [PDF]).

Otherwise, you may want to do the recursive part in your application, downloading all the data, building a tree, and walking through it.

Answer 2

Of course, since you are using MySQL, I would suggest to change you data model to nested sets or materialized path.

However, if you are stuck with adjacency list, here's a way to query it (requires creating a user defined function):

• Hierarchical queries in MySQL