Left Join Mayhem!

[Iridium Ravel]

Could someone in the know please explain to me what has happened here?

Three months ago I was running Access queries (2000 and 2002) using left joins
and there was no problem. The following code ran like a champ...

SELECT a.Company_ID, a.SourceTable, b.CompanyID, b.CompanyName
FROM a LEFT JOIN b
ON a.CompanyID = b.Company_ID
WHERE (((a.SourceTable)="thisSource"));

Today this code produces the equivalent of an inner join, only returning
matching records between the two tables.

I find the following article in the KnowledgeBase addresses my issue
http://support.microsoft.com/default.aspx?scid=kb;en-us;208880&Product=acc2000

First of all what caused my queries to stop working correctly? Was it Jet 40?
Can I revert to Jet 35? I noticed there was an article that referenced Access
2002 and 2003 that said to make sure that you have Jet 40 Service Pack 8
installed. I do. Still the same problem. Why doesn't the article for Access
2000 mention SP8? The article leads me to believe that this behavior is by
design.

It states, "When you run the query, the outer join is performed first, creating
all the records from table A. Then, the WHERE clause from table B is applied to
all the records, eliminating records from the query. The result set does not
contain all the records from table A, but contains only those for which the
condition is met for table B. " Since when?

And what about this? "Unlike Microsoft Access, SQL Server currently processes
the WHERE before the join. This is because this feature was implemented before
the ANSI SQL-92 standard. Before then, there was no outer join spec at all.
Therefore, the same query can return different results against SQL Server,
depending on whether you use a Select query or a SQL pass-through query (in
which case SQL Server handles the query and simply returns the results.) " No
outer join at all? Is this a joke? I've been using both products for ten
years. No outer join? Wah? What about all the outer joins I wrote that
worked? Did I imagine them or something?

What about apps I've designed in Access 2000? If someone upgrades to Jet 40 my
application breaks? Does anyone know what's going on here? Did MS just take
Access out behind the tool shed and beat it with an ugly stick (again)?
--

CAT, n. A soft, indestructible automaton provided by
nature to be kicked when things go wrong in the
domestic circle.

A.B. -- The Devil's Dictionary

^^^^^^^^^^^^^^^^^^^^^^^^^^
Hobbits don't get email

 

[Michel Walsh]

Hi,


The article you refer, which describes a normal behavior, is not applicable
to your case since your WHERE clause implies the preserved table.

Remove the where clause, and look at the data, is there NULL under
b.CompanyID for which a.SourceTable="thisSource"? Is it not possible that
every records in table [a] WHERE a.SourceTable="thisSource" has, indeed, a
matching record in table ?


The article would be applicable if you were using:

WHERE b.CompanyID = 444


while you probably want first make the WHERE then the join. Indeed, in SQL,
the JOIN is always made BEFORE the WHERE, and the result is then not the
same (eventually) than if we would have made the WHERE before the JOIN. The
article is just a remainder about that... and, potentially, applied only to
the WHERE conditions applied to the unpreserved tables (since otherwise, the
order of execution is irrelevant for inner join, or for outer join and
conditions on the preserved table).



Hoping it may help,
Vanderghast, Access MVP

 

[Iridium Ravel]

Hi,


The article you refer, which describes a normal behavior, is not applicable
to your case since your WHERE clause implies the preserved table.

Remove the where clause, and look at the data, is there NULL under
b.CompanyID for which a.SourceTable="thisSource"? Is it not possible that
every records in table [a] WHERE a.SourceTable="thisSource" has, indeed, a
matching record in table ?


The article would be applicable if you were using:

WHERE b.CompanyID = 444


while you probably want first make the WHERE then the join. Indeed, in SQL,
the JOIN is always made BEFORE the WHERE, and the result is then not the
same (eventually) than if we would have made the WHERE before the JOIN. The
article is just a remainder about that... and, potentially, applied only to
the WHERE conditions applied to the unpreserved tables (since otherwise, the
order of execution is irrelevant for inner join, or for outer join and
conditions on the preserved table).



Hoping it may help,
Vanderghast, Access MVP

It did, thank you.

--

CAT, n. A soft, indestructible automaton provided by
nature to be kicked when things go wrong in the
domestic circle.

A.B. -- The Devil's Dictionary

^^^^^^^^^^^^^^^^^^^^^^^^^^
Hobbits don't get email