Left outer join weird behavior

[Guest]

Hi!!!!

I have an interesting problem with a query in Access (ODBC Driver version 4.00, which I'm assuming is for Access 2000). The following is the query:

Select Table1.lngID
From (Table1
Left Join Table2 on ((Table2.lngParentID = Table1.lngID) And (Table2.lngMyFilter = 10)))
Where (Table2.lngID is null)

This query in MSSQL 7 gives all the records in Table1 that have not been associated with a record in Table2 with a specific filter criteria.

However, the same query in Access does not give any record whatsoever.

The interesting part comes into place when I remove the filter criteria (in this case, "Table2.lngMyFilter = 10"): Without the filter criteria the query does give me information.

I'm starting to think this is an Access limitation or something.

Any help is greatly appreciated!!!!

Tarh ik

 

[Allen Browne]

You are correct.

Access cannot perform an outer join on a literal value.

MS have confirmed the bug, but AFAIK have not released a k.b. article on it.
We have documented the bug in this article:
Records missed by SELECT query
at:
http://allenbrowne.com/bug-10.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an interesting problem with a query in Access (ODBC Driver version

4.00, which I'm assuming is for Access 2000). The following is the query:

Select Table1.lngID
From (Table1
Left Join Table2 on ((Table2.lngParentID = Table1.lngID) And (Table2.lngMyFilter = 10)))
Where (Table2.lngID is null)

This query in MSSQL 7 gives all the records in Table1 that have not been

associated with a record in Table2 with a specific filter criteria.

However, the same query in Access does not give any record whatsoever.

The interesting part comes into place when I remove the filter criteria

(in this case, "Table2.lngMyFilter = 10"): Without the filter criteria the
query does give me information.