Mysql Recursive Query - get all parent-child relationship [duplicate]

Question

I have table with below data.

id|child
1|2
1|3
1|4
2|6
2|7
3|9
6|10
6|11
9|12
9|13
10|14
10|15
17|18
19|20

I need to fetch the data like below. i.e all parent child relationship for a id.

Here I have select all childs recursively for parent id "1"

id|all_child_id
1|2,3,4
2|6,7
3|9
6|10,11
9|12,13
10|14,15

I am looking for "WITH RECURSIVE" which is not supported my Mysql. Can someone help me how to write such recursive query where depth must not be any constraint.

Answer 1

What your expected output shows, can be achieved by using group_concat

select parent, group_concat(child)
from table
group by parent

fiddle

Answer 2

try this ,

SELECT p.id, GROUP_CONCAT(c.child) AS all_child_id FROM parent p left JOIN 
child c WHERE p.id=c.child GROUP BY p.id