MySQL Self-Join

Question

I have a table (this cannot be changed) as shown below:

POST_ID | PARENT_ID | POST_NAME

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

The field, 'parent_id' refers to post_id to form a self-referenced foreign key. Is it possible to write a single request to connect a message and its parents based on post_id?

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

5 | Magento
4 | Development
1 | Services

I know that this is pretty easy to do with multiple queries, however, you need to know if this is possible with a single query.

Thank:)

Answer 1

, . , , .

, SQL Server 2005, Postgres 8.4 Oracle 11g, , WITH (. @Quassnoi). , , @OMG Ponies, , MySQL .

, - , ? , , , ( ), ():

• : MySQL

, @Bill Karwin, Stack Overflow:

• Bill Karwin: SQL PHP

, , . SQL Antipatterns ( [PDF]).

, , .

Answer 2

, MySQL, nested sets materialized path.

, , ( ):

• MySQL