I have two tables :
elements :
id_element | name |
---|---|
elem1 | CPV |
elem2 | CBO |
elem3 | CPV |
parenting (Which is referencing elements)
id_element | id_element_elements |
---|---|
elem1 | null |
elem2 | elem1 |
elem3 | elem1 |
elem4 | elem2 |
Is there a query to find all the child elements from a single element in this case ?
Looking for something like :
id_element | id_child |
---|---|
elem1 | elem2 |
elem1 | elem3 |
elem2 | elem4 |
Advertisement
Answer
WITH RECURSIVE cte AS ( SELECT * FROM parenting WHERE id_element_elements = 'elem1' UNION ALL SELECT parenting.* FROM cte JOIN parenting ON cte.id_element = parenting.id_element_elements ) SELECT id_element_elements id_element, id_element id_child FROM cte