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:
(IIf([ProductID] Is Null,'False','True')) AS GroupIncluded
SuperMarketGroups LEFT JOIN
(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.
© Copyright 2017 Tim Mackey