Counting the number of items which do not have an entry in a foreign table

I've got two tables. Organisations and ReturnForms.

The ReturnForm structure is:

| formID | returnMonth | returnYear | orgID |

Organisations submit return forms on a monthly basis. returnMonth and returnYear stores what month/year the form is for, orgID is the fk for which organisation submitted the form, formID is the pk for the returnForm table.

I want to count how many organisations do not have a returnForm for a specific month/year combination. For a single month/year, this is easy:

SELECT count(*) 
FROM   `tblOrganisations` AS `Organisatio开发者_JS百科n` 
       LEFT JOIN `tblReturnForms` AS `NoForm` 
         ON ( `NoForm`.`orgID` = `Organisation`.`orgID` 
              AND `NoForm`.`returnMonth` = 3 
              AND `NoForm`.`returnYear` = 2010 ) 
WHERE  `NoForm`.`formID` IS NULL 

The problem I'm having is to count how many forms have not submitted between 12/2005 and the current month. I could calculate it by running the about query for each month/year between 12/2005 and summing them, but I'm certain there is a better, more elegant way of doing it.

You can compute the earliest year and month required like so:

min_year = current_year;
min_month = current_month - 4;
if min_month < 1 then
    min_month = 12 + min_month;
    min_year = min_year - 1;
end if;

create temp table periods(year integer, month integer);
for month/year in(12/2005 .. min_month/min_year)
    insert into periods values (year, month);
loop;

and then use these in your SQL statement like so:

SELECT
    `periods`.`year`,
    `periods`.`month`,
    count(*),
FROM
    `tblOrganisations` AS `Organisation`
     JOIN periods
     LEFT JOIN `tblReturnForms` AS `NoForm` ON
         (`NoForm`.`orgID` = `Organisation`.`orgID`
          AND
          `NoForm`.`returnYear` = `period`.`year`
          AND 
          `NoForm`.`returMonth` <= `periods`.`month`))
WHERE
    `NoForm`.`formID` IS NULL
GROUP BY
    `periods`.`year`,
    `periods.`month`

This will give you number of missing returns per return type/month/year - all in one query - then just loop over the results.