0

I have a table below. I want to write mySql Query that can return all the children against the parent Id. i.e. against 0 it should return (1,2,3,4) and against 1 it should return (3,4).

I am able to do this recursive call in SQL server using "with" clause, but i want to do this same thing in MySQL.

ParentId GroupID
0 | 0
0 | 1
0 | 2
1 | 3
3 | 4

Any help in this regard is highly appreciated.

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
Imran Arshad
  • 3,794
  • 2
  • 22
  • 27
  • What are 1,2,3,4? are they groupId values ... from example expected output is not clear. – Nitin Midha Dec 29 '11 at 05:36
  • Have you tried anything? I'm not sure I fully understand if you do a select against parentid = 0 wouldn't it return 0,1,2 and if you select against 1 it would return 3? Or do you want to do recursive selects? – Robert Dec 29 '11 at 05:36
  • Possible duplicate: http://stackoverflow.com/questions/1136041/sql-tree-like-query-most-parent-group – nikc.org Dec 29 '11 at 05:44
  • Take look at this book excerpt: http://media.pragprog.com/titles/bksqla/trees.pdf – nikc.org Dec 29 '11 at 05:48
  • I want recursive select ... I am able to do this in SQL server using "With" clause, but i am not able to do it in mysql. – Imran Arshad Dec 29 '11 at 05:49
  • I believe it is possible to be done with stored procedure (program) and `cursor` (loop). – Rolice Dec 31 '11 at 13:56

1 Answers1

0

If this is an arbitrary depth hierarchy (you want ancestor instead of parent), then you can't do this directly in SQL. Google for "sql hierarchical queries" to find some possible workarounds.

Francis Upton IV
  • 19,322
  • 3
  • 53
  • 57