MySQL: get all subcategories, descendants

Question

I have mysql tables defined as:

category: category_id, category_name, parent_category_id

I am looking for a good sql query that will retrieve all the DESCENDANTS of a given category_id. This means his children and children of children.

If this helps, we can accept the maximum number of levels (3). This request can be sent at any level (root, level 2, level 3).

Thanks!

Nathan

Answer 1

There are several ways to store trees in a database. There is a fantastic article on the site that describes all the methods:

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

, , , .

. , :

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

Answer 2

: ,

PHP, PHP , . , , .

, .

Answer 3

3, :

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

, NULL parent_id.

:

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

:

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

Answer 4

MySQL , , WHILE. . The edge list .