Mysql select recursive get all child with multiple level

Question

i have a table

 CREATE TABLE IF NOT EXISTS `Folder` (
    `idFolder` INT(11) NOT NULL AUTO_INCREMENT,
    `FolderName` VARCHAR(150) NOT NULL,
    `idFolderParent` INT(11) NULL,
    PRIMARY KEY (`idFolder`),
    CONSTRAINT `fk_1`
    FOREIGN KEY (`idFolderParent`)
    REFERENCES `Folder` (`idFolder`)
)

i fill this table by

idFolder , FolderName , idFolderParent
   1           ADoc           Null  
   2           ADoc1           1  
   3           ADoc2           2
   4           ADoc3           3
   5           ADoc4           4
   6           ADoc5           5
   7           ADoc6           4

when select a folder with idFolder=1, it should be return all child for this folder and subchild folder(2, 3, 4, 5 ,6 ,7)

When i select a folder id = 4 ==> (5,7, 6)

When i select a folder id = 3 ==> (4, 5,6, 7)

How to do that with one query?

Thx

Hierarchial queries are awkward in mysql,look into multiple self LEFT joins — Mihai, Feb 06 '15 at 10:59
@Mihai MySQL now supports recursive queries: dev.mysql.com/doc/refman/8.0/en/with.html — MountainX, Nov 26 '18 at 04:21
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — trincot, Feb 26 '19 at 13:11
Please refere this answer if it may helpful. [Display Parent-Child relationship when Parent and Child are stored in same table](https://stackoverflow.com/a/59787100/10883038). — Meet Prajapati, Jan 17 '20 at 12:25

score 24 · Answer 1 · answered Nov 23 '16 at 15:30

24

None of the previous solutions worked for me. Both only work if the parents are saved into the database in a certain order.

I have to admit I do not fully understand the way the query works but could find a way that works for me (at least better than the other answers).

The data with which the first and second queries do not work is:

idFolder , FolderName , idFolderParent
   1           ADoc           Null  
   2           ADoc1           7  
   3           ADoc2           2
   4           ADoc3           3
   5           ADoc4          Null
   6           ADoc5           5
   7           ADoc6           5

If you use the first and second queries in this dataset, for the id 5 you only get as a result '6,7'. But if you use my query you get: '6,7,2,3,4' which is the expected result.

My version:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder 
WHERE FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder 
JOIN
(SELECT @pv:=5) tmp
) a;

Hope it helps someone. I cannot comment nor downvote the other answers because of lack of reputation :(

answered Nov 23 '16 at 15:30

Adrián E

1,683
2
14
24

This is actually allot better, since it allows you to use parents with both higher and lower id's compared to the children. It even works if 5 has a parent which also is a child. I swapped idFolder and idFolderParent to get only the parents (and not the children of the parents). – Jeffrey May 03 '17 at 13:32
hi, it is working fine when run individually, but when i tried in Stored Procedure then it is showing wrong output, any guess, what can be the reason. – Sandeep Oct 02 '18 at 10:12
@Sandeep no idea, but you should try creating a new question with the specific code you are running and the problem you are finding, that way anyone can help you – Adrián E Oct 02 '18 at 18:02
1

@agentprovocateur you should use SET SESSION group_concat_max_len = 1000000; immediately before your query to increase the character limit. – Adrián E Oct 02 '18 at 18:03
THis query takes 11 seconds to run! – kp123 Sep 30 '19 at 16:28

Dheerendra Kulkarni · Accepted Answer · 2015-02-06T13:06:14.050

17

Here is the working one

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder
JOIN
(SELECT @pv:=1)tmp
WHERE idFolderParent IN (@pv)) a;

Look at SQL FIddle here:http://sqlfiddle.com/#!2/02b78/1

edited Feb 06 '15 at 13:06

answered Feb 06 '15 at 12:55

Dheerendra Kulkarni

2,728
1
16
18

thx for this solution.. i need to use the result of this query into other query like : select * from xxx where idFolder IN (your query)! ..is it possible?! – Feb 06 '15 at 13:36
@Dev Dev put a query around the same to get the required result – Dheerendra Kulkarni Feb 06 '15 at 16:26
@Dheerendra Kulkarni Its not working if i add one more row as INSERT INTO `folder` (`id_folder`,`FolderName`, `idFolderParent`) VALUES (8,'ADoc7', '7') your query return only 5,7,6 but actual result is 5,6,7,8 – Sachin Sarola Feb 12 '18 at 11:09
@DheerendraKulkarni the SQL FIddle doesnot work anymore . can you update it ? – sh1hab Nov 30 '22 at 10:53
@sh1hab you canchange version of mysql to like 5.6 , build schema and run query – Dheerendra Kulkarni Dec 13 '22 at 11:37

score 8 · Answer 3 · answered Apr 25 '15 at 00:29

8

Be aware the MySQL treats the

idFolderParent IN ('1, 2')

as a single value, so it is equal to:

idFolderParent IN ('1')

so in order to operate on the list, you need to:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE
FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder
JOIN (SELECT @pv:=1)tmp
WHERE idFolderParent IN (@pv)) a;

(the FIND_IN_SET)

answered Apr 25 '15 at 00:29

asceta

272
1
4
10

2

but still not give perfect result. – Rikin Patel Jun 05 '15 at 15:26
3

The last line should be "WHERE FIND_IN_SET(idFolderParent, @pv) a;" – Magnar Myrtveit Oct 17 '16 at 11:55

score 3 · Answer 4 · answered Dec 05 '16 at 12:03

select  idFolder, FolderName, idFolderParent
                                from (select * from Folder order by idFolderParent, idFolder) folders_sorted,
                                (select @pv := 1) initialisation
                                where   find_in_set(idFolderParent, @pv) > 0
                                and @pv := concat(@pv, ',', idFolder)

@pv := 1 is your current Folder id.. I think this is much better solution.

Dheerendra Kulkarni's answer which is below gives me this error for different collations. I hope this will helps someone.

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
    SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder
    JOIN
    (SELECT @pv:=1)tmp
    WHERE idFolderParent IN (@pv)) a;

Hi @Oğuz Can Sertel. the first query i have used and successfully implemented. Thank you. But want some more on that I want to show the child id having a specific data. can you please help me on that. — Binayak Das, Sep 19 '17 at 22:13

score 3 · Answer 5 · answered Feb 12 '18 at 12:26

May be it works if you don't want to main id in result remove SELECT 4 Level UNION from query

SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM ( SELECT 4 Level UNION SELECT @Ids := (SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM folder WHERE FIND_IN_SET(idFolderParent, @Ids)) Level FROM folder JOIN (SELECT @Ids := 4) temp1 ) temp2

Look at SQL FIddle here : http://sqlfiddle.com/#!9/a2b4b3/312

score -1 · Answer 6 · answered Dec 09 '17 at 08:43

to get all level child of a particular parent you can use this below query:

 select idFolder from (select * from Folder order by idFolderParent, idFolder) Folder, (select @pv := '1') initialisation where find_in_set(idFolderParent, @pv) > 0 and @pv := concat(@pv, ',', idFolder )

Mysql select recursive get all child with multiple level

6 Answers6

Linked

Related