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

Solution 1

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

Solution 2

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)

Solution 3

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));

Solution 4

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)

Solution 5

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

View more solutions

Comments

[Bakti Wijaya]

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

[Mathews Sunny]

Please explain your code segment. Just a piece of code won't be much helpful for users.

[Manoj Rana]

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

[Elby]

Yes this works very fine for all levels. Perfect code

[Do Hoa Vinh]

this will not work if there are many children because the concat function returns string length limited

[jor]

This query is NOT recursive!

[Katty]

@ManojRana It's working fine but Im unable to add where clause in this query like WHERE userType <> 'A'

[Katty]

Is it recursive?

[Katty]

and how to add count clause with this query?

[hhsadiq]

perfectly worked, but couldn't understand iota of it, but thanks a lot.

[Codetector]

can you please explain how this works? It would be greatly helpful.

[Zbigniew Jasek]

Here is an explanation: stackoverflow.com/questions/20215744/…

[kp123]

@ZbigniewJasek do you know how I can modify this query to ALSO return the parent row as the first row?

[kp123]

@ManojRana do you know how to modify this query to ALSO return the parent row as the first row?

[Muaaz Khalid]

Does not seem to work on all levels if there are children that have lower IDs than their parents. Any Solution??

[jeewiya]

@muaaz Did you find any solution for lower parent ids, Also I have same issue

[Muaaz Khalid]

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.

[jeewiya]

I have found this article from the stackoverflow comments, It's worked for me Hierarchical queries in MySQL/ Hierarchical data in MySQL: parents and children in one query/

[Y. Joy Ch. Singha]

Great. great great . thanks. for saving my time. Thanks.

[Steve Moretz]

this is NOT recursive

[Sumit]

the := operator can be escaped in Hibernate's createNativeQuery String like this: @pv \\:= concat(@pv, ',', id)

[amaster]

why does this not have more votes?? Thank you @Thomas Lobker!