Mysql getting all the children

Question

Say I have a data base with classes and requirements that looks like this for example:

+--------+------+
| course |prereq|
+--------+------+
| 1      |  null|
| 2      |  null|
| 3      |    1 |
| 3      |    2 |
| 4      |    3 |
| 5      |    4 |
+--------+------+

And I want to find all the prereqs for course 4. So my answer would be 3,2,1.
Also this is just an example I want to be able to use this for a table of any size.

How would I go about doing this in mysql?

Please update the question with your query? what efforts have u made — Digital Alchemist, Jul 30 '13 at 03:55
are the courses always numerically and in the proper order starting at 1? or was your example overly simple? — amaster, Jul 30 '13 at 04:00
I fully addressed how to do this with Stored Procedures in the DBA StackExchange : http://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161 — RolandoMySQLDBA, Jul 30 '13 at 04:57

score 2 · Answer 1 · edited Apr 13 '17 at 12:42

2

What you are asking is about hierarchical joins in mysql.

With a sort of logic and a stored function will do the trick for you.

This thread surely will lead you.

Have a try.

edited Apr 13 '17 at 12:42

Community

1
1

answered Jul 30 '13 at 03:56

Praveen Prasannan

7,093
10
50
70

1

+1 I love the link to a google search! Makes the OP do some research, but sometimes knowing what to search for is the tricky part – amaster Jul 30 '13 at 04:03

Mysql getting all the children

1 Answers1