mySQL aggregate query with hireachy Article Table:

ID | INT(10)
Category | INT(10)


Category Table:

ID | INT(10)
Parent | INT(10)


What I want to generate is a tree that includes a count of articles at all levels e.g.

A (30)
--&gt; B (20)
--&gt; --&gt; C (10)
--&gt; --&gt; D (10)
--&gt; --&gt; --&gt; Q (6)
--&gt; --&gt; --&gt; L (4)
--&gt; E (5)
--&gt; F (5)
G (8)
--&gt; H (8)
--&gt; --&gt; I (7)
--&gt; --&gt; J (1)


I'd really like to be able to generate this output in a single SQL query; however I can do anything to the database structure if that helped. At the moment the only way we can see to do this is:

SELECT COUNT(*) FROM articles GROUP BY category


and then create the hireachy in code. That's what we are trying to avoid.

The only other approach I can think of is a complicated IF() statement which is auto generated based on a pre-computed list of ID hierachies.
Nope.

The one query is a SELECT that fetches everything, then your application code does all the formatting work.

Or you make a messy Stored Procedure to do all that work.  And you spend longer than you would like debugging it.