Microsoft Access 97/XP Left Join On Multiple Columns Fails - Vista/7 are fine

Question

Im trying to do a left join on two tables to get all values that do not exist in table 2 or have a status of null:

Table 1 has ROID as long

Table 2 has ID long, Type long, and Status text.

This works fine on Vista and Win 7 (I get all the records that do not exist in V), but I get no records on XP,

  SELECT roid
  FROM
  wo AS w LEFT JOIN VFlag As V ON (w.roid = V.ID AND V.Type = 2)
  WHERE
  (V.Status is Null)

When I remove the V.Type = 2, it works fine on XP/Vista/7, but I need to qualify on Type as well:

SELECT roid
  FROM
  wo AS w LEFT JOIN VFlag As V ON (w.roid = V.ID)
  WHERE
  (V.Status is Null)

This is both in VB6 using ADO and VisData. Trying WHERE isnull(V.Status) did not make any difference. XP machines (two tested on) are SP3.

score 1 · Accepted Answer · answered Jan 10 '12 at 17:23

1

Have you tried placing the V.Type = 2 in the WHERE clause instead of the join?

answered Jan 10 '12 at 17:23

ron tornambe

10,452
7
33
60

Tried, doesnt work. It may not have a matching ID in that table. – Luke D Jan 10 '12 at 17:26
Just curious, have you tried seeing what values V.Type have ("SELECT, roid, V.Type...") after removing it from the ON when running on XP? on Vista/7? Do all tables on all O/S's contain the same data? – ron tornambe Jan 10 '12 at 17:53
Yep, I copied the database to several machines. Going on what you said earlier, I think I might be able to get it by doing WHERE (V.Type is Null or V.Type = 2). Id still rather find the reason why though. – Luke D Jan 10 '12 at 17:58
I think you're on to something. When there is no matching key on an outer join, all fields are set to null. Since you are selecting only rows that have no matching key (V.Status is Null), V.Type is always going to null. I do not understand however why the first example would work on Vista/7 though? Do you get the correct results when leaving out the V.Type test on all platforms? Am Imissing something here? – ron tornambe Jan 10 '12 at 18:07
Its definitely the way nulls are handled in XP vs Vista/7. Simply changing the left join to LEFT JOIN VFlag As V ON (w.roid = V.ID AND (V.Type = 2 or V.Type is null)) works, so Ill go with that as its easiest. Tired all sorts of Jet updates, and nothing. Either way, good enough for me. – Luke D Jan 10 '12 at 18:37
Interesting, but I still think the V.Type=2 (or any V.Type test) is unnecessary. V.Type should always be Null with this query. You should get exactly the same results when removing it. HTH – ron tornambe Jan 10 '12 at 18:42
Its necessary because something that is of a different Type (ie Type 3), may already be in this table, and the ID is the same. ID is not a PK/unique in that table. – Luke D Jan 10 '12 at 18:53
Ok, applying Jet 3.51 SP3 fixed everything, so now it behaves the same across all OSes with the original query. – Luke D Jan 10 '12 at 19:20

score 0 · Answer 2 · answered Feb 28 '14 at 16:42

0

I am a bit late to this - but I just came across what seems to be the same issue on Access 2003 Seems that the bug is fixed in latest JET. So could well explain the symptoms you saw and how fixed on Windows 7.

MS KB275058

mikecro

answered Feb 28 '14 at 16:42

mikecro

101
1
1
6

score 0 · Answer 3 · edited May 23 '17 at 11:55

First, it is a known issue that Access's outer join is non-compliant with Standards, yields unexpected results and is less expressive as a result. The SQL Server team wanted to fix this 'bug' but was scuppered by the Windows Team; it is higly unlikely that the engine has been fixed subsequently.

Second, nulls in SQL are a disaster generally. The SQL Standard fails to define three-valued logic. Access specifically also fails to three-valued logic and has numerous inconsistencies. It is best to avoid the null value and outer join is expressly designed to generate nulls.

Third, there is always more than way to express the same thing in SQL. The relational operator you require is semi difference a.k.a. antijoin. Your spec reads, "get all values that do not exist in table 2" so consider using NOT EXISTS e.g.

SELECT roid
  FROM wo AS w 
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM VFlag As V 
                    WHERE w.roid = V.ID 
                          AND V.Type = 2
                  );

I'm not sure whether the subquery should additionally test V.Type IS NULL because you've included implementation details (LEFT OUTER JOIN) in your spec and I can't see the trees for the wood :)

Microsoft Access 97/XP Left Join On Multiple Columns Fails - Vista/7 are fine

3 Answers3