By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

left join bug (?) in MS-Access 2000 SR-1

P: n/a
pj
/* 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)
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.