Skip to content
Advertisement

MySQL query to find all child elements

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

fiddle

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement