By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Subquery Where or inner join and a left outer join

P: 2
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 ?
Dec 22 '16 #1

✓ answered by jforbes

I think all you need to do is include A1 and B1 in your Join:
Expand|Select|Wrap|Line Numbers
  1. FROM A LEFT JOIN B ON A.A1 = B.B1 AND A.A2 = B.B2

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
I think all you need to do is include A1 and B1 in your Join:
Expand|Select|Wrap|Line Numbers
  1. FROM A LEFT JOIN B ON A.A1 = B.B1 AND A.A2 = B.B2
Dec 22 '16 #2

P: 2
Thank you jforbes, that is the answer I was looking for.
Dec 22 '16 #3

Post your reply

Sign in to post your reply or Sign up for a free account.