How to find all IDs of children recursively?

There are two basic methods for doing this: adjacency lists and nested lists. Take a look at Managing Hierarchical Data in MySQL.

What you have is an adjacency list. No there isn't a way of recursively grabbing all descendants with a single SQL statement. If possible, just grab them all and map them all in code.

Nested sets can do what you want but I tend to avoid it because the cost of inserting a record is high and it's error-prone.

Here is a simple single-query MySql-solution:

SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (   SELECT @Ids := (       SELECT GROUP_CONCAT(`ID` SEPARATOR ',')       FROM `table_name`       WHERE FIND_IN_SET(`parent_id`, @Ids)   ) Level   FROM `table_name`   JOIN (SELECT @Ids := <id>) temp1) temp2

Just substitute <id> with the parent element's ID.

This will return a string with the IDs of all descendants of the element with ID = <id>, separated by ,. If you would rather have multiple rows returned, with one descendant on each row, you can use something like this:

SELECT *FROM `table_name`WHERE FIND_IN_SET(`ID`, (   SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (      SELECT @Ids := (          SELECT GROUP_CONCAT(`ID` SEPARATOR ',')          FROM `table_name`          WHERE FIND_IN_SET(`parent_id`, @Ids)      ) Level      FROM `table_name`      JOIN (SELECT @Ids := <id>) temp1   ) temp2))

Including the root/parent element

The OP asked for the children of an element, which is answered above. In some cases it might be useful to include the root/parent element in the result. Here are my suggested solutions:

Comma-separated string of ids:

SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (   SELECT <id> Level   UNION   SELECT @Ids := (       SELECT GROUP_CONCAT(`ID` SEPARATOR ',')       FROM `table_name`       WHERE FIND_IN_SET(`parent_id`, @Ids)   ) Level   FROM `table_name`   JOIN (SELECT @Ids := <id>) temp1) temp2

Multiple rows:

SELECT *FROM `table_name`WHERE `ID` = <id> OR FIND_IN_SET(`ID`, (   SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (      SELECT @Ids := (          SELECT GROUP_CONCAT(`ID` SEPARATOR ',')          FROM `table_name`          WHERE FIND_IN_SET(`parent_id`, @Ids)      ) Level      FROM `table_name`      JOIN (SELECT @Ids := <id>) temp1   ) temp2))

You could probably do it with a stored procedure, if that's an option for you.

Otherwise you can't do it with a single sql-statement.

Ideally you should make the recursive calls to walk the tree from your program