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 (
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
Answers
-
Have you tried writing it like this:
SELECT p.`id`, p.`name`, p.parent_id, c.`name` as childname, FROM `products` p INNER JOIN `products` c on p.id = c.parentid
You could change the inner join to a left join in case there are products with no children and you want to still include those.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
I believe that would work if everything belonged in the same hierarchy. However, if you have different hierarchies how do you distinguish them? For example, with the table below, your query would bring all the relations including the 30 and 31 which are not related with the id=19
0 -
MySQL 5.6 does not support CTEs.
this approach works.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
@jaeW_at_Onyx Thanks for letting me know that MySQL version in Domo is MySQL 5.6
I had followed that query example you sent for the 5.6 version and it definitely works when the parent_id < id. However in my use case, I have records in which the parent_id > id so I guess I'll have to stick with the while loop for now.
I will have that example into account if my dataset has an hierarchy where all records fulfill the condition parent_id < id but it would be good to know if Domo has in its pipeline any upgrade of the MySQL version in the near future.
0 -
@Fatias MySQL 5.65 is several years old. I'm pretty sure it's safe to say it's not a priority to upgrade the version of MySQL to something more modern.
you don't have to implemen using parent_id < id. you can implement where you accumulate the path
concat(path '+', id) as path
and just filter
WHERE path not like '%id%'
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive