MySQL query to find all child elements

Question

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

Answer 1

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