Outer join adds calculated value to all records

[JimJ]

I'm finding in Access 2003 SP2 that, when I do a left outer join, a
calculated field in the right query is propagated to all records in
the outer join, not just those in common with the right table.

Here is the setup:

Table "T1" has just one field. The field is named "Well" and has
entries of "A", "B", "C", and "D".

Query "Q1_Select" adds a calculated field to selected records from
Table "T1":

SELECT T1.Well, "Hi" AS Flag
FROM T1
WHERE (((T1.Well) Between "A" And "B"));

The result, as expected, is:

Well Flag
---- ----
A Hi
B Hi

Query "Q2_fromQ1" does an outer join to include all records of Table
T1 and the matching records from Query "Q1_Select":

SELECT T1.Well, Q1_Select.Well, Q1_Select.Flag
FROM T1 LEFT JOIN Q1_Select ON T1.Well = Q1_Select.Well;

The result is:

T1.Well Q1_Select.Well Flag
------- -------------- ----
A A Hi
B B Hi
C Hi
D Hi

The "Flag" is added to the records that did not come from "Q1_Select".
This was not expected.

This experiment was repeated using a table instead of a query on the
right side of the join. This produced the expected result of:

T1.Well Q1_Select.Well Flag
------- -------------- ----
A A Hi
B B Hi
C
D

So what is going on here? Is there a workaround?

Any help would be appreciated,
JRJ

 

[Guest]

One solution:

SELECT T1.Well,
Q1.Well AS Q_Well,
IIf(IsNull([Q_Well])=0,"Hi",Null) AS Flag
FROM T1 LEFT JOIN (SELECT T1.Well
FROM T1
WHERE T1.Well In("A", "B")) AS Q1
ON T1.Well = Q1.Well;

Notice that I used an In statement instead of a Between for the A and B. In
is a little more accurate if, for example, you had A, AB, B in the table.

 

[JimJ]

OK, Jerry, Thanks, That works. I can use it. But I'm still wondering
what was wrong with my original approach. As I understand it, you
combined the two queries into one. Am I not thinking about this
right, or is there a bug in Access?

Thanks,
JimJ

One solution:

SELECT T1.Well,
Q1.Well AS Q_Well,
IIf(IsNull([Q_Well])=0,"Hi",Null) AS Flag
FROM T1 LEFT JOIN (SELECT T1.Well
FROM T1
WHERE T1.Well In("A", "B")) AS Q1
ON T1.Well = Q1.Well;

Notice that I used an In statement instead of a Between for the A and B. In
is a little more accurate if, for example, you had A, AB, B in the table.

 

[Guest]

Not a bug at all. Your first SQL statement says to fill the Flag field with
"Hi" for every record. That carries over to the next query also even with the
left join.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

OK, Jerry, Thanks, That works. I can use it. But I'm still wondering
what was wrong with my original approach. As I understand it, you
combined the two queries into one. Am I not thinking about this
right, or is there a bug in Access?

Thanks,
JimJ

One solution:

SELECT T1.Well,
Q1.Well AS Q_Well,
IIf(IsNull([Q_Well])=0,"Hi",Null) AS Flag
FROM T1 LEFT JOIN (SELECT T1.Well
FROM T1
WHERE T1.Well In("A", "B")) AS Q1
ON T1.Well = Q1.Well;

Notice that I used an In statement instead of a Between for the A and B. In
is a little more accurate if, for example, you had A, AB, B in the table.

I'm finding in Access 2003 SP2 that, when I do a left outer join, a
calculated field in the right query is propagated to all records in
the outer join, not just those in common with the right table.

Here is the setup:

Table "T1" has just one field. The field is named "Well" and has
entries of "A", "B", "C", and "D".

Query "Q1_Select" adds a calculated field to selected records from
Table "T1":

SELECT T1.Well, "Hi" AS Flag
FROM T1
WHERE (((T1.Well) Between "A" And "B"));

The result, as expected, is:

Well Flag
---- ----
A Hi
B Hi

Query "Q2_fromQ1" does an outer join to include all records of Table
T1 and the matching records from Query "Q1_Select":

SELECT T1.Well, Q1_Select.Well, Q1_Select.Flag
FROM T1 LEFT JOIN Q1_Select ON T1.Well = Q1_Select.Well;

The result is:

T1.Well Q1_Select.Well Flag
------- -------------- ----
A A Hi
B B Hi
C Hi
D Hi

The "Flag" is added to the records that did not come from "Q1_Select".
This was not expected.

This experiment was repeated using a table instead of a query on the
right side of the join. This produced the expected result of:

T1.Well Q1_Select.Well Flag
------- -------------- ----
A A Hi
B B Hi
C
D

So what is going on here? Is there a workaround?

Any help would be appreciated,
JRJ