How do I count fields across tables when some counts may be zero?

This is a tricky one and Stanislav Yordanov has answered this nicely in the comments in the MySQL manual. The answer is to LEFT JOIN from. Say you had a number of articles by single authors and you wanted a quick count of them the you would do something like this:

SELECT *,

          COUNT(artlcle.article_id) AS article_count

     FROM author 

LEFT JOIN article

       ON author.author_id = article.author_id

 GROUP BY author.author_id

Note: You can also get all the other author information out and use them so you have all your information at the same time - saving you have to do a separate count (further WHERE clauses go between the ON and GROUP BY).

--------------------------
Relevant links:

3.3.4.8 Counting Rows

___________________
Emperor

(Added by: Emperor on Tue 05-Aug-2003)

(Edited by: Emperor on Tue 05-Aug-2003)