Subquery Where or inner join and a left outer join

[Erik Dekker]

I have 2 tables (A and B), their setup is the same (datatypes of equivalent fields are the same)

Table A
ID A1 A2
1 1 $2.00
2 2 $3.00
3 3 $4.50
4 4 $4.75
5 4 $5.00
6 4 $6.00

Table B
ID B1 B2
1 1 $4.75
2 2 $4.50
3 3 $3.00
5 4 $2.00
6 4 $5.00
7 4 $6.00

What I want to achieve is a list of values of A2 that do not exist in B2, BUT only when the value of A1 = B1

A simple left outer join
SELECT A.A1, A.A2, B.B2
FROM A LEFT JOIN B ON A.A2 = B.B2
WHERE (((B.B2) Is Null));
will show A1=4 and A2 is $5.00, but that is not the result I am after.

It should show
A1 A2
1 $2.00
2 $3.00
3 $4.50
4 $4.75

I have been struggling to add an extra clause to the WHERE statement, but I get unhelpful errors.

Any suggestions ?

[jforbes]

I think all you need to do is include A1 and B1 in your Join:

FROM A LEFT JOIN B ON A.A1 = B.B1 AND A.A2 = B.B2

[Erik Dekker]

Thank you jforbes, that is the answer I was looking for.