SQL DataFlows

SQL DataFlows

Is it possible to do CTE hierarchical recursive query in MySQL?

Member
edited March 2023 in SQL DataFlows

I have a table with thousands of rows in which there is a relationship of child/parent between the rows. For each record I would like to have all the hierarchy related with that record.

As an example, I have the following table and for the id=19 I would like to return all its related children, which are [20, 21, 22]




I have done this with a while loop in MySQL and it is working. However it is is taking some time and I believe with recursive CTE would be faster. I tried the following code for a single record only:

WITH 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;


However this code is returning an error of syntax "You have an error in your SQL syntax" and I am wondering if the MySQL version of Domo does not support this.

Thanks

Tagged:

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Answers

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In