Select all children from infinite parent-child relations

Question

I have a comments table, and rows have parent child relation, which means any comment can have children (responses) and any child itself can also have children (infinitely).

All rows are inside one table, and root row's parent_id is 0.

Now I want to select all children (in any deep level) of a given parent_id

Any idea ?

Answer 1

Regarding the "SELECT", i.e. how to recursively get all the descendants of a parent, there a lot of answers in this question:

Find highest level of a hierarchical field: with vs without CTEs

• Rolando's method uses variables and a procedure.

• a_horse_with_no_name's method uses a recursive CTE. This is not possible in MySQL yet but you could switch to MariaDB 10.2 which has them implemented.

Hierarchical queries in MySQL

• The blog post by @Quassnoi has detailed explanation about writing recursive queries in MySQL.

---

For the recursive DELETE, there is a simple method available in all MySQL versions:

• Add a FOERIGN KEY constraint - if you haven't already - with the ON DELETE CASCADE option. Then a simple delete of a node will delete all its descendants.

• You could modify the methods in the above links, to do a DELETE instead of SELECT.