Trying to understand MySQL self joins

Question

I'm trying to understand how MySQL recursive queries work and to get the data the way I want it.

Right now I have the following SQL

SELECT `Library`.*, l2.*, l3.*
FROM `Library`
LEFT JOIN `Library` as `l2` on `l2`.`subsectionOf`=`Library`.`iD`
LEFT JOIN `Library` as `l3` on `l3`.`subsectionOf`=`l2`.`iD`
WHERE `Library`.`iD` = 0x02
ORDER BY `Library`.`subsectionOrder`, `l2`.`subsectionOrder`, `l3`.`subsectionOrder`

This gets me a 3-level-deep hierarchy of data laid out as:

Layer 1 fields, Layer 2 fields, Layer 3 fields 

1. How to I make the query work for n levels of depth?

2. How do I make the results stack on top of each other rather than adding fields to the right?

   Layer 1 Row
   Layer 2 Row
   Layer 2 Row
   Layer 3 Row
   Layer 3 Row
   Layer 2 Row
   Layer 3 Row
   Layer 2 Row
   

Answer 1

MySQL does not support recursive SQL queries. Most people store hierarchical data differently so they can get whole trees in a single query.

See my answer to What is the most efficient/elegant way to parse a flat table into a tree?

I have a presentation Models for Hierarchical Data with SQL and PHP, and I wrote about tree-structured data in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Another tricky solution is given by @Quassnoi in his answer to Hierarchical queries in MySQL here on SO.

Answer 2

Your query is not a recursive query; it's just a table self-joining itself 3 times.

A 5 minutes Google search will show you that, as of now, MySQL does not support truly recursive queries.

Answer 3

MySQL does not support recursive querying.

In other databases, you can perform recursion by using Common Table Expressions (CTEs), for example with SQL Server, Postgres, and DB2. With Oracle, you can use CTEs (as of 11g), or the non-standard CONNECT BY. MySQL does not support either of these options.