MySQL: Get all subcategories, descendants

Question

I have mysql tables defined as:

categories: category_id, category_name, parent_category_id

I'm looking for a nice sql query that would retrieve all the DESCENDANTS of a given category_id. That means, its children, and its children's children.

If that helps, we can assume a maximum number of levels (3). This query could be sent at any level (root, level 2, level 3).

Thanks!

Nathan

Answer 1

There are a few ways to store trees in a database. There's a fantastic article on sitepoint describing all the techniques:

http://articles.sitepoint.com/article/hierarchical-data-database/2

The one that is most appropriate if you want to be able to get an entire section of a tree in one query is Modified Preorder Tree Traversal.

This technique is also known as Nested Sets. There's more information here if you want more literature on the subject:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Answer 2

It can be done in a single query and a piece of recursive backend code logic: Formatting a multi-level menu using only one query.

If you also do PHP, this article comes with a PHP example as bonus, but translating to another language isn't that hard. I can't give any hints about that as you didn't mention the server side language you're using.

Hope this helps.