how to create a mysql hierarchical recursive query

Question

I have a MySQL table which is as follows:

id	name	parent_id
19	category1	0
20	category2	19
21	category3	20
22	category4	21
...	...	...

Now, I want to have a single MySQL query to which I simply supply the id [for instance say id=19] then I should get all its child ids [i.e. result should have ids '20,21,22']....

The hierarchy of the children is not known; it can vary....

I know how to do it using a for loop... but how to achieve the same using a single MySQL query?

Answer 1

Let’s create a hierarchical recursive query taking into consideration MYSQL version 8 and above.

This can be done with help of syntax :

with recursive cte (id, name, parent_id) as (

  select     id,

             name,

             parent_id

  from       products

  where      parent_id = 20

  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;

 

So here the value inn parent_id=20 is set to the id of the parent of whose descendants or child you want to select.

Curious about Mysql, check out this MYSQL Course by Intellipaat.