Unfortunately, if you can’t change the data model, and you’re using MySQL, you’re stuck in a situation where you need recursive queries and you’re using a DBMS that doesn’t support recursive queries.
Quassnoi wrote an interesting series of blog articles, showing techniques for querying hierarchical data. His solutions are quite clever, but very complex.
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/
PostgreSQL is another open-source RDBMS, which does support recursive queries, so you could fetch a whole tree stored in the way you show. But if you can’t change the data model, I’d assume you can’t switch to a different RDBMS.
There are several alternative data models that make it much easier to fetch arbitrarily-deep trees:
- Closure Table
- Nested Sets aka Modified Preorder Tree Traversal
- Path Enumeration aka Materialized Path
I cover these in my presentation Models for Hierarchical Data with SQL and PHP, and in my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
Finally, there’s another solution that I’ve seen used in the code for Slashdot, for their comments hierarchies: They store “parent_id” like in Adjacency List, but they also store a “root_id” column. Every member of a given tree has the same value for root_id, which is the highest ancestor node in its tree. Then it’s easy to fetch a whole tree in one query:
SELECT * FROM site WHERE root_id = 123;
Then your application fetches all the nodes back from the database into an array, and you have to write the code to loop over this array, inserting the nodes into a tree data structure in memory. This is a good solution if you have many separate trees, and each tree has relatively few entries. It’s good for Slashdot’s case.