Mysql Query to select multi-level children from table

Question

This is my MySQL table layout of user table.
Is it possible to

1. Get the count of children under each person.
2. To select the person with at least 2 children under him.

+----+------+--------+
| id | Name | Parent |
+----+------+--------+
| 1  |    A |      0 |
+----+------+--------+
| 2  |    B |      0 |
+----+------+--------+
| 3  |    C |      1 |
+----+------+--------+
| 4  |    D |      3 |
+----+------+--------+
| 5  |    E |      2 |
+----+------+--------+

The expected answer are
1.

+----+------+----------+
| id | Name | Children |
+----+------+----------+
| 1  |    A |  2(C, D) |
+----+------+----------+
| 2  |    B |     1(E) |
+----+------+----------+
| 3  |    C |     1(D) |
+----+------+----------+
| 4  |    D |        0 |
+----+------+----------+
| 5  |    E |        0 |
+----+------+----------+

2.

+----+------+----------+
| id | Name | Children |
+----+------+----------+
| 1  |    A |  2(C, D) |
+----+------+----------+

Answer 1

Recursive queries are not supported by MySQL (edit: since I answer this question in 2014, MySQL 8.0 does support recursive queries), so this problem is quite awkward to solve if you store the data the way you store it (with parent indicating the hierarchical relationship).

You can store hierarchies in a few different ways to make this problem easier. I did a presentation about this here: Models for Hierarchical Data with SQL and PHP.

My favorite solution I call Closure Table. In this design, you use a second table to store all paths in the hierarchy. Include paths of length zero, which connect each node to itself, because this makes some tasks easier later.

CREATE TABLE TreePaths (
  ancestor INT NOT NULL,
  descendant INT NOT NULL,
  length INT NOT NULL DEFAULT 0
  PRIMARY KEY (ancestor, descendant)
);

INSERT INTO TreePaths VALUES
(1,1,0), (1,3,1), (1,4,2),
(2,2,0), (2,5,1),
(3,3,0), (3,4,1),
(4,4,0),
(5,5,0);

Then you can query all children for a given node:

SELECT descendant FROM TreePaths WHERE ancestor = 1 AND length > 0;

You can limit this to nodes with at least two children by grouping by ancestor and using HAVING to pick the group:

SELECT ancestor, COUNT(*), GROUP_CONCAT(descendant) FROM TreePaths WHERE length > 0
GROUP BY ancestor HAVING COUNT(*) >= 2;