Tim Mackey's Weblog

I was doing a straight forward left join to include all the records of a table, regardless of whether they had records in the related table.  However, i added a where clause, which produced very strange results.  Some of the foreign records were not included in the results, specifically those that had no related records at all in the main table.  This was very frustrating to pin down, but i finally found out that the where clause in a left or right join should not use the 'where' syntax.

instead, it should use:

SELECT
    SuperMarketGroups.GroupName,
    SuperMarketGroups.ID, 
    ProductsInGroups.GroupProductCode, 
    ProductsInGroups.MinimumStoreRating, 
    ProductsInGroups.ProductID, 
    (IIf([ProductID] Is Null,'False','True')) AS GroupIncluded
FROM 
    SuperMarketGroups LEFT JOIN 
    ProductsInGroups ON
        (SuperMarketGroups.ID = ProductsInGroups.GroupID 
            AND (ProductsInGroups.ProductID Is Null OR ProductsInGroups.ProductID=?)
        )

note that there is no 'where' clause.  the extra condition is specified with the join columns. this solved my problem perfectly.