Mysql select recursive get all child with multiple level

by

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 🙁

More Related Contents:

  1. When I want to install MySQL I faced this issue “root account password” I forgot it
  2. How to perform grouped ranking in MySQL
  3. How to abort INSERT operation in MySql trigger?
  4. Restoring MySQL database from physical files
  5. MySQL Trigger after update only if row has changed
  6. MySQL “WITH” clause
  7. MySQL Query – Records between Today and Last 30 Days
  8. MySQL “Group By” and “Order By”
  9. MySQL Query to select data from last week?
  10. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
  11. Migrate from Oracle to MySQL
  12. Which is more efficient: Multiple MySQL tables or one large table?
  13. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  14. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  15. MySQL ‘user_id’ in where clause is ambiguous problem
  16. Images in MySQL
  17. What does it mean when MySQL is in the state “Sending data”?
  18. MySQL JSON: How to find object from key value
  19. Advanced MySql Query: Update table with info from another table
  20. Commit data in a mysql container
  21. How do I count columns of a table
  22. Mysqldump launched by cron and password security
  23. MySQL Trigger cannot update table – getting ERROR 1442
  24. Why MySQL is giving error “Not allowed to return a result set from a function”?
  25. Where I can find a list of “mysqldump” exit codes?
  26. Query to count the number of tables I have in MySQL
  27. `MODIFY COLUMN` vs `CHANGE COLUMN`
  28. INSERT INTO Table from multiple tables
  29. Mysql How do you create a clustered index?
  30. Counting associated entries with Sequelize

Leave a Comment