SELECT Sites_1.permid, Sites_1.site_name, Sites_1.status,; Sites_1.startdate, History_1.permid, History_1._year, History_1.xyear,; History_1.xbaseyear, History_1.xsiteid, History_1.surveys_distributed,; History_1.validsurveys, History_1.responserate, History_1.afv,; FROM ; NEWBLUEBOOK!SITES Sites_1 ; LEFT OUTER JOIN NEWSURVEYS!HISTORY History_1 ; ON Sites_1.permid = History_1.permid AND History_1._year = 2005 ; WHERE Sites_1.status = ( "TRP" ); ORDER BY Sites_1.startdateWhen you have condition on the table referenced in the LEFT JOIN in the where clause you're making the LEFT JOIN operate as INNER JOIN.
>SELECT Sites_1.permid, Sites_1.site_name, Sites_1.status,; > Sites_1.startdate, History_1.permid, History_1._year, History_1.xyear,; > History_1.xbaseyear, History_1.xsiteid, History_1.surveys_distributed,; > History_1.validsurveys, History_1.responserate, History_1.afv,; > FROM ; > NEWBLUEBOOK!SITES Sites_1 ; > LEFT OUTER JOIN NEWSURVEYS!HISTORY History_1 ; > ON Sites_1.permid = History_1.permid; > WHERE Sites_1.status = ( "TRP" ); > AND History_1._year = ( 2005.0000 ); > ORDER BY Sites_1.startdate >>It produces 91 records with a startdate in 1989 (and others) which is what we would expect. If I change the _year in the where clause to 2007, it produces 51 records with a startdate in 1989 which is not what I want. With the left join I would expect to still get all of the records where sites.status = "TRP", in this case 91 records, and the history fields as NULL if there is no match. But it looks like the where clause is filtering.