query help - LEFT OUTTER JOIN with a WHERE clause

[Guest]

Have a table1 with cust_no, field1, field2. have table2 with cust_no, field3.
want to add field3 to table2, but only if field3 > 5. Want to end up with the
same number of record in table1. The WHERE field3 > 5 makes me lose records
from table1.

Tried a LEFT OUTER JOIN on cust_no WHERE field3 > 5, but records get dropped
from table1 it has a match but the field3 < 6.

thanks

Brenda

 

[Michel Walsh]

Hi,


Apply the test before making the join:


SELECT ...
FROM a LEFT JOIN (SELECT *
FROM myTable
WHERE fieldY > 5) As b
ON a. xxx = b.zzz



I assume the test was about a field in the unpreserved table (the right
table for a left join).



Hoping it may help,
Vanderghast, Access MVP