Is it possible to do CTE hierarchical recursive query in MySQL?
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 (
WHERE parent_id = 19
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.