hierarchical data - Mysql Query to select multi-level children from table -

this mysql table layout of user table.
possible

1. get count of children under each person.
2. to select person @ least 2 children under him.

+----+------+--------+ | id | name | parent | +----+------+--------+ | 1  |    |      0 | +----+------+--------+ | 2  |    b |      0 | +----+------+--------+ | 3  |    c |      1 | +----+------+--------+ | 4  |    d |      3 | +----+------+--------+ | 5  |    e |      2 | +----+------+--------+ 

the expected answer are
1.

+----+------+----------+ | id | name | children | +----+------+----------+ | 1  |    |  2(c, d) | +----+------+----------+ | 2  |    b |     1(e) | +----+------+----------+ | 3  |    c |     1(d) | +----+------+----------+ | 4  |    d |        0 | +----+------+----------+ | 5  |    e |        0 | +----+------+----------+ 

2.

+----+------+----------+ | id | name | children | +----+------+----------+ | 1  |    |  2(c, d) | +----+------+----------+ 

recursive queries not supported mysql, problem quite awkward solve if store data way store (with parent indicating hierarchical relationship).

you can store hierarchies in few different ways make problem easier. did presentation here: models hierarchical data sql , php.

my favorite solution call closure table. in design, use second table store paths in hierarchy. include paths of length zero, connect each node itself, because makes 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 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 can query children given node:

select descendant treepaths ancestor = 1 , length > 0; 

you can limit nodes @ least 2 children grouping ancestor , using having pick group:

select ancestor, count(*), group_concat(descendant) treepaths length > 0 group ancestor having count(*) >= 2;