/* Make two tables, then find that a left join between them works,
unless you add a view on top of one table -- if you add a view and
use it, the left join fails -- rather, it acts as an inner join */
aaTable1
myid, Number
typeid, Number
1,11
1,13
2,11
2,13
aaTable2
myid, Number
name, Text
typeid, Number
1, bob, 13
2, charlie, 13
/* query on top of table2 */
SELECT 13 AS typeid, *
FROM aaTable2;
/* buggy query -- only yields 2 rows */
SELECT aaTable1.typeid, aaTable1.myid, aaViewTable2.name
FROM aaTable1 LEFT JOIN aaViewTable2 ON (aaTable1.myid =
aaViewTable2.myid) AND (aaTable1.typeid = aaViewTable2.typeid);
/* working query -- yields 4 rows */
SELECT aaTable1.myid, aaTable1.typeid, aaTable2.name
FROM aaTable1 LEFT JOIN aaTable2 ON (aaTable1.typeid =
aaTable2.typeid) AND (aaTable1.myid = aaTable2.myid);
This reminds me of a similar bug I found in MS SQL Server (and I was
informed that every version of MS SQL Server suffers from this bug).
MS-Access 2000 (9.0.3821SR-1)
