I have a database that I want to be able to query 3 tables on.
On two of the tables I want to actually retrieve stored values, on the 3rd table I want to perform a count of articles that data generated by the first two.

Table 1: Main Categories (maincat)
maincatid: (INT)
maincatname: (VARCHAR)

Table 2: Sub Categories (subcat)
subcatid: (INT)
maincatid: (INT) -> foreign key constrained to maincat
subcatname: (VARCHAR)

Table 3: Articles (articleindex)
artindexid: (INT)
maincatid: (INT) -> foreign key constrained to maincat
subcatid: (INT) -> foreign key constrained to subcat

I want a query that retrieves all the maincats and subcats and performs a count of the ammount of articles that match each one.

This would allow me to generate a PHP categories table that would look something like (The numbers in brackets being the number or articles in each category):
MainCat1 (23)
---SubCat1 (9)
---SubCat2 (4)
---SubCat3 (8)
---SubCat4 (2)
MainCat2 (14)
---SubCat5 (7)
---SubCat6 (0)
---SubCat7 (4)
---SubCat8 (3)

So far the closest I've been able to come up with is this, but this only returns the number of articles that match the subcat (I add them together in PHP to make the maincat total):
----
SELECT mc.maincatname, mc.maincatid, sc.subcatname, sc.subcatid, COUNT(ai.subcatid)
FROM maincat mc
LEFT OUTER JOIN subcat sc ON (sc.maincatid = mc.maincatid)
LEFT OUTER JOIN articleindex ai ON (sc.subcatid = ai.subcatid)
GROUP BY mc.maincatname, sc.subcatname
ORDER BY mc.maincatname, sc.subcatname ASC;

Any suggestions, or am I trying to achieve the impossible?

thanks for any help.

Seri