0

I am trying to fetch all the children of given id on mysql version 5.7.37

id name parent_id
1 post1 0
2 post2 1
3 post3 6
4 post4 6
5 post5 6
6 post6 0

this solution works but it is for newer versions of mysql

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       posts
  where      parent_id = 6
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       posts p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

this solution have a bug in it and it wont work if you are trying to fetch id 6 from given table

select * from (select * from posts) posts, (select @pv := 6) initialisation where (find_in_set(parent_id, @pv) > 0 and @pv := concat(@pv, ',', id)) OR id = 6

i can use stored procedure or functions also. Please suggest the solution.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
atul1039
  • 143
  • 4
  • 13
  • 1
    My first choice would be to upgrade to MySQL 8.0 if you need recursive queries. It has been the current GA version since 2018. MySQL 5.7 will be end-of-life in October 2023, which is 16 months from now as we write this. You really need to be thinking about your upgrade if you need the features of the new version. – Bill Karwin Jun 24 '22 at 15:09
  • In the meantime you might like my answer to [What is the most efficient/elegant way to parse a flat table into a tree?](https://stackoverflow.com/a/192462/20860) or my presentation [Models for hierarchical data](https://www.slideshare.net/billkarwin/models-for-hierarchical-data) or the chapter on hierarchical queries in my book [SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming](https://pragprog.com/titles/bksap1/sql-antipatterns-volume-1/). – Bill Karwin Jun 24 '22 at 15:12

0 Answers0