MYSQL - How to create a MySQL hierarchical recursive query

https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query 

MySQL 8+

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

The value specified in parent_id = 19 should be set to the id of the parent you want to select all the descendants of.

MySQL 5.x

For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the following query:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))