Categories
hierarchical-data mysql recursive-query sql

How to create a MySQL hierarchical recursive query?

404

I have a MySQL table which is as follows:

idnameparent_id
19category10
20category219
21category320
22category421

Now, I want to have a single MySQL query to which I simply supply the id [for instance say id=19] then I should get all its child ids [i.e. result should have ids ‘20,21,22’]….

The hierarchy of the children is not known; it can vary….

I know how to do it using a for loop… but how to achieve the same using a single MySQL query?

5

  • Suppose the hierarchy is 7 levels deep. What do you expect the output table to look like?

    Nov 26, 2013 at 11:48

  • 1

    MySQL (still) doesn’t support hierarchical queries (as other modern DBMS do). You will need to write a stored procedure or use a different datamodel.

    Nov 26, 2013 at 12:33

  • 1

  • 2

    MYSQL 8.0 will support Recursive query using CTE (Common Table Expressions)

    Aug 3, 2017 at 18:38

  • What about getting the full list of posts starting from the last comment id? Or the last child?

    – joe

    Mar 21, 2018 at 2:55


90

From the blog Managing Hierarchical Data in MySQL

Table structure

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Query:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name="ELECTRONICS";

Output

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.
Read more

Refer the blog for more details.

EDIT:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where [email protected]

Output:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

Reference: How to do the Recursive SELECT query in Mysql?

15

  • 29

    That’s fine as long as there are no more than 4 levels at most in the hierarchy. If there are N levels, you have to know that to create the query properly.

    Nov 26, 2013 at 11:47

  • 2

    @Damodaran, Thanks for your reply… What I needed is a condition where the number of childs are not known… and in the blog that is using a inner join concept in that the hierarchy is required to be known which is not in my case… so let me know your view on the same… So, in simple words I need a query to handle ‘n’ hirerachy levels where ‘n’ is not known…..

    Nov 26, 2013 at 11:48


  • 1

    @user3036105: it is not possible to do this in MySQL with a single SQL query. MySQL simply isn’t advanced enough for that. If you really need this, consider upgrading to a DBMS which supports recursive queries.

    Nov 26, 2013 at 12:40

  • 5

    >Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. Maybe you meant a MySQL database. An Oracle database handles hierarchical data and queries quite well.

    Sep 18, 2014 at 21:30


  • 1

    “…the management of hierarchical data is not what a relational database is intended for…” While this may have not been the original intention of a relational database, in the real-world hierarchical data is incredibly commonplace and MySQL should reflect how people actually need to use their data in real-world scenarios.

    – Dave L

    Sep 28, 2016 at 14:57

17

Try these:

Table definition:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

Experimental rows:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Recursive Stored procedure:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, "https://stackoverflow.com/", catname);
    END IF;
END$$
DELIMITER ;

Wrapper function for the stored procedure:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Select example:

SELECT id, name, getpath(id) AS path FROM category;

Output:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

Filtering rows with certain path:

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

Output:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+

5

  • 1

    This won’t work for more then one child. e.g (20, 'category2', 19), (21, 'category3', 20), (22, 'category4', 20),

    Apr 3, 2018 at 6:54

  • 6

    I’m pretty sure it works for more than one child. I even tested it again.

    Apr 7, 2018 at 14:12

  • @Fandi Susanto , Thanks it alote helps me.

    Mar 9, 2019 at 6:34


  • The solution worked for my but it is important to check if the top level parents (categories) are identified by a parent_id which is NULL or 0. Accordingly, the tempparent check must look like: IF (tempparent IS NULL OR tempparent = 0)

    – Benjamin

    Sep 6, 2020 at 17:16


  • Thanks man ! worked well for me had just to change IF tempparent IS NULL to IF tempparent = 0 in my case

    Nov 12, 2021 at 16:40