group by - MYSQL query to count descendants of items by finding its id in a comma separated string -

my mysql database uses tree-like system each item can have arbitrary number of descendants. each item has regular int 'parent' column containing parent varchar 'parents' column consists of comma-separated string containing of ancestor's ids.

id    parent    parents ------------------------------- 1     0         0 2     1         0,1 3     1         0,1 4     3         0,1,3 

i need list of items, each of them total number of descendants counted up. here query have far:

select items.id item_id,  count(children.id) children  items items  left join items children on (items.id in (children.parents)) 

this sends 1 row, child count of 0. how do properly?

edit:

after fixing query appears this:

select    i.id item_id,    count(*) children     items  left join    items c    on (i.id in (c.parents)) group i.id; 

the results show rows, each has 1 child. not reflect data, presumably wrong in statement (find_in_set same thing).

edit2:

after changing in statement following

on locate(i.id, c.parents) > 0 

item 1 has correct number of children (3) remaining items show having 1 child. items 2 , 4 should have 0, , 3 should have 1.

you need group items.id count()to work intended.

with aliases changed less ambiguous:

select    i.id item_id,    count(*) children     items  left join    items c    on find_in_set(i.id, c.parents) > 0  c.id <> i.id group i.id; 

for more complex count()/group examples, see this question or mysql documentation. find_in_set(), nice example here.

see sqlfiddle here