SubQuery on Single Table

Ok so I have a table

Tasks
--
TaskId  (unique autoinc primary)
ChildOf   (Contains task ID of parent, or 0 if top tier (no parent))

I need to write a query that selects all records with ChildOf = 0 ... Simple right ?

Ok but also need to have another column returned with the results that tells how many Children each Task has ...

So result would look like this ...

TaskID ...  ChildOf ... countChildren
 37   ......  0   ....    3
 42   ......  0   ....    0
 99   ......  0   ....    1 

etc.... 

I know the two queries I 开发者_如何学编程need are something like this, but need to combine them somehow...

Select TaskId as ParentTaskId, ChildOf from Tasks where ChildOf = 0

and

Select count(TaskId) from Tasks where ChildOf = ParentTaskId

NOTE: There are only 2 tiers.. Parent and Child ... No Grandchildren! So hopefully that makes it a bit less complicated.

Any help is greatly appreciated. Thanks for all the help so far!

Something like this should do it:

SELECT TaskId as ParentTaskId, ChildOf , 
 (SELECT count(t2.TaskId) 
  FROM Tasks t2
  WHERE t2.ChildOf = t.TaskId) as CountChildren
FROM Tasks t 
WHERE t.ChildOf = 0

Try this:

SELECT T1.TaskID, T1.ChildOf, count(*) from 
  Tasks as T1 join Tasks as T2 on T1.TaskID = T2.ChildOf 
  WHERE T1.ChildOf = 0 
    GROUP BY (T1.TaskID)