MySQL: Get all subcategories, descendants

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

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/

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.

If you want to use this structure with max 3 levels you can join the table to itself three times:

SELECT 
    c1.id AS level_1, 
    c2.id AS level_2, 
    c3.id AS level_3
FROM categories c1
LEFT JOIN categories c2 ON c1.id = c2.parent_id
LEFT JOIN categories c3 ON c2.id = c3.parent_id
WHERE c1.parent_id IS NULL

I assume, that parent categories have NULL in parent_id.

Some example:

DECLARE @categories TABLE 
(
    id INT,
    parent_id INT
)

INSERT INTO @categories(id,parent_id) VALUES(1,NULL)
INSERT INTO @categories(id,parent_id) VALUES(4,1)
INSERT INTO @categories(id,parent_id) VALUES(5,1)
INSERT INTO @categories(id,parent_id) VALUES(6,5)
INSERT INTO @categories(id,parent_id) VALUES(2,NULL)

SELECT * FROM @categories

SELECT c1.id AS level_1, c2.id AS level_2, 
    c3.id AS level_3
FROM @categories c1
LEFT JOIN @categories c2 ON c1.id = c2.parent_id
LEFT JOIN @categories c3 ON c2.id = c3.parent_id
WHERE c1.parent_id IS NULL

Returns:

level_1 | level_2 | level_3
---------------------------
1       | 4       | NULL
1       | 5       | 6
2       | NULL    | NULL

MySQL does not support recursive queries, but you can find all descendants using a stored procedure with a WHILE loop. See the The edge list section of this book sample.