MySQL Recursive get all child from parent

Question

i have this case using recursive query on Mysql to find lv 2 and lv3 child on one table...
database structure i'm using:

id name parent
1    A    0
2    B    0
3    C    0
4    D    1
5    E    1
6    F    2
7    G    2
8    H    3
9    I    3
10   J    4
11   K    4

The result i was expecting, when filtering the data, where id=1, it will generate the result i'm expecting.

id name parent
4   D     1
5   E     1
10  J     4
11  K     4

or this is the illustration.

i've been looking everywhere, and reading this http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/, but i didn't find the result i was looking for..
any help would be appreciated, thanks

insert your table name, and the page you linked does not exists — ollaw, Jan 28 '17 at 18:59

score 50 · Answer 1 · answered Dec 09 '17 at 08:33

50

if you want to get all level child of a particular parent then you should try this

select  id,
        name,
        parent
from    (select * from tablename
         order by parent, id) tablename,
        (select @pv := '1') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', id)

answered Dec 09 '17 at 08:33

Manoj Rana

3,068
1
24
34

9

Please explain your code segment. Just a piece of code won't be much helpful for users. – Mathews Sunny Dec 09 '17 at 09:03
this query is helpful if you want to get all child of a parent and also their child and so on. or simply we can say that this is useful to get recursive data – Manoj Rana Dec 09 '17 at 09:22
Yes this works very fine for all levels. Perfect code – Elby Sep 27 '18 at 07:09
this will not work if there are many children because the concat function returns string length limited – Do Hoa Vinh Oct 28 '18 at 01:10
@ManojRana It's working fine but Im unable to add where clause in this query like WHERE userType <> 'A' – Katty Dec 20 '18 at 05:44
3

perfectly worked, but couldn't understand iota of it, but thanks a lot. – hhsadiq Mar 18 '19 at 14:23
1

can you please explain how this works? It would be greatly helpful. – Codetector May 25 '19 at 15:32
Here is an explanation: https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Zbigniew Jasek Aug 26 '19 at 22:04
@ZbigniewJasek do you know how I can modify this query to ALSO return the parent row as the first row? – kp123 Sep 30 '19 at 16:58
@ManojRana do you know how to modify this query to ALSO return the parent row as the first row? – kp123 Sep 30 '19 at 16:58
6

Does not seem to work on all levels if there are children that have lower IDs than their parents. Any Solution?? – Muaaz Khalid Oct 30 '19 at 12:48
1

@muaaz Did you find any solution for lower parent ids, Also I have same issue – jeewiya Nov 25 '19 at 05:12
1

unfortunately no. I had to handle all that logic at the code level, and that's really a pain as we can't switch to Oracle at this stage. Note: Oracle supports recursive procedures and functions. – Muaaz Khalid Nov 25 '19 at 14:16
I have found this article from the stackoverflow comments, It's worked for me [Hierarchical queries in MySQL](https://explainextended.com/2009/03/17/hierarchical-queries-in-mysql)/ [Hierarchical data in MySQL: parents and children in one query](https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/)/ – jeewiya Nov 27 '19 at 02:50
Great. great great . thanks. for saving my time. Thanks. – Y. Joy Ch. Singha Apr 12 '20 at 13:11

Thomas Lobker · Answer 2 · 2021-08-05T22:11:08.203

In my opinion the key to finding all children in a hierarchical table structure is to first find the path to the parent, and then use FIND_IN_SET to see if the requested node is in the path. It's easier and more efficient to search up than down, because the link to the parent is already there in the table.

So let's start with a hierarchy like this:

1 Pets
├─ 2 Dogs
│  ├─ 3 Katie
├─ 4 Cats
│  ├─ 5 George
│  ├─ 6 Pete
│  ├─ 7 Alice
├─ 8 Other
│  ├─ 9 Rabbits
│  │  ├─ 10 Noah
│  │  ├─ 11 Teddy
│  │  ├─ 12 Bella
│  ├─ 13 Rats
│  │  ├─ 14 Henry

And now you want to find all children under the category Other (with the category included) then the expected result would be:

8,9,10,11,12,13,14

Now let's have a look at the hierarchical path of Henry. The parent for Henry (14) is Rats (13), which has parent Other (8) and finally Pets (1). If we use the ID's to make a path for Henry it would look like this:

1,8,13,14

And this is where the MySQL function FIND_IN_SET kicks in. With FIND_IN_SET you can filter results where a variable can be found in a comma separated list. In this example we are looking for all children in the category Other (8) and we can simply use FIND_IN_SET(8, path).

To get the path for a hierarchical table I would like to refer to my answer in the post here MySql: ORDER BY parent and child. We will just change the dash to a comma, so we can use the FIND_IN_SET function.

The example above would look like this in a hierarchical table:

+----+--------+---------+
| id | parent | name    |
+----+--------+---------+
|  1 |   NULL | Pets    |
|  2 |      1 | Dogs    |
|  3 |      2 | Katie   |
|  4 |      1 | Cats    |
|  5 |      4 | George  |
|  6 |      4 | Pete    |
|  7 |      4 | Alice   |
|  8 |      1 | Other   |
|  9 |      8 | Rabbits |
| 10 |      9 | Noah    |
| 11 |      9 | Teddy   |
| 12 |      9 | Bella   |
| 13 |      8 | Rats    |
| 14 |     13 | Henry   |
+----+--------+---------+

In my approach I will use a procedure that will recursively call itself and keep prepending the path with the parent of the requested id until it reaches the NULL parent.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PATH`(IN `input` INT, OUT `output` VARCHAR(128))
BEGIN

  DECLARE _id INT;
  DECLARE _parent INT;
  DECLARE _path VARCHAR(128);

  SET `max_sp_recursion_depth` = 50;

  SELECT `id`, `parent`
  INTO _id, _parent
  FROM `database`.`table`
  WHERE `table`.`id` = `input`;

  IF _parent IS NULL THEN
    SET _path = _id;
  ELSE
    CALL `PATH`(_parent, _path);
    SELECT CONCAT(_path, ',', _id) INTO _path;
  END IF;

  SELECT _path INTO `output`;

END $$
DELIMITER ;

We need these results in a SELECT query so we need a FUNCTION too that wraps the results of the PROCEDURE.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GETPATH`(`input` INT) RETURNS VARCHAR(128)
BEGIN

  CALL `PATH`(`input`, @path);
  RETURN @path;

END $$
DELIMITER ;

Now we can use the path in a query. On a table with 10000 rows it takes just over a second on my workstation.

SELECT `id`, `parent`, `name`, GETPATH(`id`) `path` FROM `database`.`table`;

Example output:

+----+--------+---------+-----------+
| id | parent | name    | path      |
+----+--------+---------+-----------+
|  1 |   NULL | Pets    | 1         |
|  2 |      1 | Dogs    | 1,2       |
|  3 |      2 | Katie   | 1,2,3     |
|  4 |      1 | Cats    | 1,4       |
|  5 |      4 | George  | 1,4,5     |
|  6 |      4 | Pete    | 1,4,6     |
|  7 |      4 | Alice   | 1,4,7     |
|  8 |      1 | Other   | 1,8       |
|  9 |      8 | Rabbits | 1,8,9     |
| 10 |      9 | Noah    | 1,8,9,10  |
| 11 |      9 | Teddy   | 1,8,9,11  |
| 12 |      9 | Bella   | 1,8,9,12  |
| 13 |      8 | Rats    | 1,8,13    |
| 14 |     13 | Henry   | 1,8,13,14 |
+----+--------+---------+-----------+

And to find all children of Other (8) with Other itself also included we can use the same query and filter with FIND_IN_SET:

SELECT `id`, `parent`, `name`, GETPATH(`id`) `path` FROM `database`.`table` WHERE FIND_IN_SET(8, GETPATH(`id`));

And finally here is the result. We put a recursion limit of 50 levels in the procedure, but besides that we have no limit in the depth.

+----+--------+---------+-----------+
| id | parent | name    | path      |
+----+--------+---------+-----------+
|  8 |      1 | Other   | 1,8       |
|  9 |      8 | Rabbits | 1,8,9     |
| 10 |      9 | Noah    | 1,8,9,10  |
| 11 |      9 | Teddy   | 1,8,9,11  |
| 12 |      9 | Bella   | 1,8,9,12  |
| 13 |      8 | Rats    | 1,8,13    |
| 14 |     13 | Henry   | 1,8,13,14 |
+----+--------+---------+-----------+
7 rows in set (0,01 sec)

If you want to have a single value instead of rows then you might want to use GROUP_CONCAT like this:

SELECT GROUP_CONCAT(`id`) `children` FROM `database`.`table` WHERE FIND_IN_SET(8, GETPATH(`id`));

Giving you the following result:

+--------------------+
| children           |
+--------------------+
| 8,9,10,11,12,13,14 |
+--------------------+
1 row in set (0,00 sec)

why does this not have more votes?? Thank you @Thomas Lobker! — amaster, May 04 '22 at 01:49
It would be great if you can handle a recursive loop in case of invalid data... — Tushar, Aug 07 '22 at 06:07

score 4 · Answer 3 · edited Oct 31 '20 at 05:52

4

Try this one , much simple and easy to understand.

(but supports only one hierarchy level)

SET @pv = 1;
select * from tablename 
where FIND_IN_SET(parentrecordID,@pv) and !isnull(@pv:= concat(@pv, ',', id));

edited Oct 31 '20 at 05:52

Oleg Cherr

3,565
2
17
27

answered May 16 '20 at 17:40

Sam

1,106
10
14

score 1 · Answer 4 · answered Dec 19 '19 at 08:23

I tried this one

select  id from  (select * from roles order by parent_role, id) roles,(select @pv := '1') initialisation
where   find_in_set(parent_role, @pv) > 0
and     @pv := concat(@pv, ',', id)

but it works for depth 2 only, i need to make it work with more depth since i have 8 levels

score 1 · Answer 5 · answered Jan 17 '20 at 13:01

1

Thanks @Manoj Rana your solution is really helped me a lot. But I want to use this solution in Hibernate createNativeQuery(); function. Because of := operator I can't able to use. So I prepared new stored procedure using your solution and use it in my code.

You can find The stored procedure which I have created in this link

answered Jan 17 '20 at 13:01

Meet Prajapati

191
2
7

the `:=` operator can be escaped in Hibernate's `createNativeQuery` String like this: `@pv \\:= concat(@pv, ',', id)` – Sumit Aug 23 '21 at 00:49

score 0 · Answer 6 · answered Jan 17 '20 at 20:40

The answer you are looking for can be this; https://github.com/ersengultepe/mysql_hierarchy_recursive_procedure/

    DROP PROCEDURE IF EXISTS store_procedure_name;
CREATE PROCEDURE `store_procedure_name`(IN cat_id INT)
BEGIN
    declare loopId Int;
    SET max_sp_recursion_depth = 255;
    -- If the value of the category that comes as a parameter is not in the table as parent_id, no further action is required
    IF(select count(id) from category_table where parent_id=cat_id) > 0 THEN

    -- create temporary table
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_category_table (
      `id` smallint(5) unsigned,
      `status` tinyint(3)
      ) ENGINE=InnoDB ;
    -- First, save the corresponding value in the temporary table.
    INSERT INTO temp_category_table
    (id, status) 
    VALUES (cat_id, 0);

    -- continue loop as long as the appropriate record exists in the temporary table
    WHILE (select count(id) from temp_category_table where status=0) > 0 DO 
      -- in this section, a record with a status of 0 will be extracted from the temporary table and assigned to the variable loopId
      set loopId = (select id from temp_category_table where status=0 limit 1);

      INSERT INTO temp_category_table
      (id, status)                                       
      (select id, 0 from category_table where parent_id=loopId);

      update temp_category_table set status=1 where id=loopId;

      CALL store_procedure_name((select id from temp_category_table where status=0 limit 1));     

    END WHILE;

    (select DISTINCT(id) from temp_category_table order by id ); 

    END IF;
END;

score -1 · Answer 7 · edited Mar 21 '21 at 10:42

-1

Try this, Much faster

SELECT * 
FROM table AS T1 
INNER JOIN (SELECT id FROM table WHERE parent = 1) AS T2 
ON T2.id = T1.parent OR T1.parent = 1 
GROUP BY T1.id

edited Mar 21 '21 at 10:42

matrix

3,000
3
24
35

answered Sep 01 '17 at 11:27

pradeep

550
4
10

Is it recursive? – Katty Dec 20 '18 at 05:52
and how to add count clause with this query? – Katty Dec 20 '18 at 08:17
4

this is NOT recursive – Steve Moretz Jan 12 '21 at 09:06

score -10 · Accepted Answer · answered Jan 28 '17 at 18:59

-10

SELECT *
FROM TABLENAME
WHERE PARENT = 1
UNION
SELECT * 
FROM TABLENAME
WHERE PARENT IN 
    (SELECT ID FROM TABLENAME WHERE PARENT = 1)

answered Jan 28 '17 at 18:59

ollaw

2,086
1
20
33

that was fast,thank you..i was using the same quey,but it seems that i was wrong writing the query on where parent IN(SELECT ID FROM TABLENAME WHERE PARENT = 1). @Ollaw – Bakti Wijaya Jan 28 '17 at 19:03
25

This query is NOT recursive! – jor Nov 08 '18 at 13:41

MySQL Recursive get all child from parent

8 Answers8

Linked

Related