471,084 Members | 826 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,084 software developers and data experts.

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

/* 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 */

myid, Number
typeid, Number

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 2292

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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.