php - MySQL parent -> child query

Question

I'm working with mySQL, and I'm in a situation where I need to select data from one table that matches an ID at any level in parent -> child data hierarchy in the other table.

Further more, I would like to resolve this with a well written SQL query, rather than a recursive function in my PHP code, as this feature will be used quite a bit.

I did try searching, and I have stumbled upon numerous similar problems (most of them being resolved), however none of them helped me.

To help illustrate the situation, here's my current setup

table "articles":

• article_id
• category_id
• ...

table categories

• category_id
• parent_id
• ...

I need to select all the articles from "articles" where "articles.category_id" is, let's say, 10. But also receive all the articles from all categories from the tree the "categories.category_id" 10 belongs to.

Meaning, where "10" is the parent and all of it's children, and upwards where 10 is the child and all of it's parents.

Possible without a recursive php function?

Thank you.

Answer 1

It is not possible to fetch an entire tree in one query using the Adjacency List design you're using, given that you're using MySQL.

Some other brands of database support SQL extensions to handle this kind of design. Oracle, Microsoft SQL Server, IBM DB2, and PostgreSQL 8.4 (currently in beta) support SQL extensions.

Other database designs exist that allow you to query trees more efficiently. This question has been addressed many times on StackOverflow, on blogs, and in articles.

You can also read "Trees and Hierarchies in SQL for Smarties" by Joe Celko, which goes into several such designs in depth.