Left Join weird problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a 3 tabel (A-B-C) query like this:
SELECT .... FROM (A LEFT JOIN B) LEFT JOIN C
when I execute it, it returns a wrong result (right number of records, wrong
NULL value in some fields). If I change it like this:
SELECT .... FROM (A LEFT JOIN C) LEFT JOIN B
I obtain the right result (right number of records and right not NULL values
in some fileds).
I'm using Access 2000 from a long long time but I never see a thing like
this ... I have searched all the kb and a lot of forums without find
something usefull.
 
Roberto Fabbrica said:
I have a 3 tabel (A-B-C) query like this:
SELECT .... FROM (A LEFT JOIN B) LEFT JOIN C
when I execute it, it returns a wrong result (right number of records, wrong
NULL value in some fields). If I change it like this:
SELECT .... FROM (A LEFT JOIN C) LEFT JOIN B
I obtain the right result (right number of records and right not NULL values
in some fileds).
Click to expand...
Hi Roberto,

You left out the ON clauses in your example
which can make a big difference in "cascading
outer joins."

Simple example:

table A
fA
1
2
3

table B
fB
1
2

table C
fC
2
3
------------
SELECT A.fA, B.fB, C.fC
FROM (A LEFT JOIN B ON A.fA = B.fB)
LEFT JOIN C ON A.fA = C.fC;

fA fB fC
1 1
2 2 2
3 3
------------
SELECT A.fA, B.fB, C.fC
FROM (A LEFT JOIN B ON A.fA = B.fB)
LEFT JOIN C ON B.fB = C.fC;

fA fB fC
1 1
2 2 2
3
-------------
Both queries "start" with an outer join
between A and B

SELECT A.fA, B.fB
FROM A LEFT JOIN B ON A.fA = B.fB;

fA fB
1 1
2 2
3

but each gave *different results* depending
on whether fA or fB is then joined
to C.fC.

Does this help?

Gary Walter
 
Back
Top