MySQL Forums
Forum List  »  General

CTE with multiple references to the recursive table
Posted by: Oliver Demetz
Date: November 30, 2019 06:08AM

This query does work under postgres and I need something similar for mysql:

```
WITH RECURSIVE dependent(id) AS (
SELECT id FROM data_category WHERE id = :cat
UNION (
WITH parents AS ( SELECT * FROM dependent)
SELECT child_id AS id
FROM data_category_1 dc
INNER JOIN parents ON dc.id = parents.id
UNION
SELECT child_id AS id
FROM data_category_2 dc
INNER JOIN parents ON dc.id = parents.id
)
)
SELECT id from dependent
```
There are two problems:
1. I think the nested (WITH parents...) is a problem
2. I use this to only access the recursive part once, is this possible in any other way?

Options: ReplyQuote


Subject
Written By
Posted
CTE with multiple references to the recursive table
November 30, 2019 06:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.