4

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

Nathan H
  • 48,033
  • 60
  • 165
  • 247

4 Answers4

5

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/

jonnii
  • 28,019
  • 8
  • 80
  • 108
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.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
1

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
Lukasz Lysik
  • 10,462
  • 3
  • 51
  • 72
  • I like where you're getting at with this one, except the end format isn't really easy to use (this whole query is to then be used as a subquery such as WHERE categoryId IN (list of call descendants). Could we make a query that would generate a cache table that would have columns: parent_category_id, descendant. It would list all the descendant for every given category? – Nathan H Nov 06 '09 at 22:59
  • It's not a solution, just a patch, cause in common case we cannot predict categories level. And, at least, it's very bad to hardcode it. – Thelambofgoat Nov 30 '16 at 08:40
  • 1
    @Lambrusco wow 7 years after the answer. Care to give proper solution? – Lukasz Lysik Nov 30 '16 at 09:56
  • @LukaszLysik Sorry I didn't read the question completely, is states, that we can accept that only three levels are used. For multi-level solution, we can either select subcategories recursively based on parent id or select them all and then traverse recursively, the solutions are here http://stackoverflow.com/questions/2398402/recursive-function-to-get-all-the-child-categories – Thelambofgoat Nov 30 '16 at 12:15
  • @Lambrusco Why would you dig out old question, put totally useless comment and then vote the answer down? – Lukasz Lysik Nov 30 '16 at 17:24
  • This is not about how new or old the question is, this is about it holding top position on specific Google query and so getting pretty much viewed. And my answer is not "useless", it's, again, about bad programming. One would just use the solution and then one day his program would be broken. – Thelambofgoat Dec 01 '16 at 08:07
0

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404