1

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.

Gaurav Pant
  • 4,029
  • 6
  • 31
  • 54
  • 1
    You need to write a stored procedure or do it in your programming language. It's not possible with a single SQL query in MySQL. –  May 14 '14 at 06:58
  • @vhu - i tried this but not getting results as i want. – Gaurav Pant May 14 '14 at 07:02
  • I'm not saying that the answers in that question would solution to your problem, but just that it's a duplicate question. – vhu May 14 '14 at 07:04

2 Answers2

3

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

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

fiddle

G one
  • 2,679
  • 2
  • 14
  • 18
0

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
ravikumar
  • 893
  • 1
  • 8
  • 12