Hello...

I could really use some feedback on this query scenario:

There are 'dates' and 'counties' tables:

CREATE TABLE `counties` ( `id` smallint(5) unsigned NOT NULL auto_increment, -- [...] `name` varchar(25) NOT NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM

CREATE TABLE `dates` ( `date` date default NULL, `time` time default NULL, `eventid` smallint(5) unsigned NOT NULL, `county` smallint(5) unsigned default '99', -- [...] PRIMARY KEY (`id`) ) TYPE=MyISAM

In trying to add a monthly calendar-type way to view the data, I'd like to have an HTML select box that lists the counties and in parentheses the number of scheduled dates for that county, during the selected month. If no dates are scheduled, the county should still be listed, only a zero should appear in the parentheses.

I have it working, but it sure seems like a round-about way to do it, and I was wondering if anyone could see a better way:

CREATE TEMPORARY TABLE cxd SELECT dates.county AS id, COUNT(dates.county) AS count FROM dates WHERE DATE_FORMAT(dates.date,'%m%Y') = ${month}${year} GROUP BY id

Then...

SELECT c.id, c.name, IF(cxd.count > 0,cxd.count,0) AS count FROM counties c LEFT JOIN cxd ON cxd.id = c.id WHERE c.id < 99 GROUP BY c.id ORDER BY c.name

I have tried a single query such as:

SELECT c.id, c.name, IF(count(d.county) > 0,count(d.county),0) AS count FROM counties c LEFT JOIN dates d ON d.county = c.id WHERE c.id < 99 AND DATE_FORMAT(d.date, '%m%Y') = ${month}${year} GROUP BY c.id ORDER BY c.name

But it doesn't return dates with a zero county count.

Many thanks in advance!

Bill