Select all the child of a parent

Question

i have this table on MySQL

+----+-----------+------------+
| ID | ID_parent | name       |
+----+-----------+------------+
| 7  | 5         | ....       |
| 10 | 7         | ....       |
| 11 | 5         | ....       |
| 20 | 7         | ....       |
| 30 | 10        | ....       |
| 45 | 20        | ....       |

For the ID=7 How can I select all child that its anchor is 7 => (10,20,30,45) ?

Then it's not possible with one query when you have table in this format. There are special table formats which enable you to query them like this. Or do each level with one query and do the recursion in PHP or whatever language you use. — Marki555, May 26 '15 at 17:56
I'm not so sure it's impossible. According to [this article](http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html) recursice ctes are supported on MySql. — Zohar Peled, May 26 '15 at 18:22
look for `CONNECT BY` or better its `MySQL` alternative, see [here](http://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql) ... heck, `MySQL` doesn't support anything beside the plain join, it seems. — davidhigh, May 26 '15 at 18:53

score 0 · Answer 1 · answered May 27 '15 at 22:35

We dcide to add a extra column ancestors where we put all the ancestor parent of a child separated by a , like this

+----+-----------+------------+------------+
| ID | ID_parent | name       | ancestors  |
+----+-----------+------------+------------+
| 7  | 5         | ....       | ,7,        |
| 10 | 7         | ....       | ,10,7,     |
| 11 | 5         | ....       | ,11,5,     |
| 20 | 7         | ....       | ,20,7,     |
| 30 | 10        | ....       | ,30,10,7,  |
| 45 | 20        | ....       | ,45,20,7,  |

In my case i choose to put the child and it's ancestors between , and i start and i finish with the , maybe different approach can be used depend on situation.

So now we can easily get the child of a parent or get the parent ancestor of a child.

To found all child of a parent we use LIKE %,idparent,%
To found all parents of a child we split the field ancestors of this child and we can get all ancestor so we loop over them.

Select all the child of a parent

1 Answers1