468,539 Members | 1,814 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

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

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
0 2221

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Marek Lewczuk | last post: by
reply views Thread by Petre Agenbag | last post: by
1 post views Thread by Paul Bramscher | last post: by
3 posts views Thread by Ian Boyd | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.