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