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

left outer join with subselect

P: 1
Hi, I am trying to perform a single select of data from 2 tables, table A and table B.

Table B may have none, one or many corresponding rows.

If table B has no corresponding rows then table B values should be set to null.

If table B has one or more corresponding rows then table B values should be set to the corresponding table B row with the lowest DATE.

I think I should do a left outer join on table A and table B but can't work out how to code the MIN(DATE) subselect.

I've tried the following but it doesn't work:

Select
from TABLE_A A LEFT OUTER JOIN TABLE_B B
ON A.REF_NO = B.REF_NO
AND B.DATE =
(SELECT MIN(DATE)
FROM TABLE_B C
WHERE B.REF_NO = C.REF_NO)

Does anyone have any ideas?

Thanks.
Apr 17 '07 #1
Share this Question
Share on Google+
4 Replies


frozenmist
Expert 100+
P: 179
I think this small change should work...

Select
from TABLE_A A LEFT OUTER JOIN TABLE_B B
ON A.REF_NO = B.REF_NO
where B.DATE =
(SELECT MIN(DATE)
FROM TABLE_B C
WHERE B.REF_NO = C.REF_NO)

Try this .. hope this was useful
May 2 '07 #2

P: 1
If you want rows from table A when there are no corresponding rows in B that satisfy the condition then you'll need to add a clause:

Select
from TABLE_A A LEFT OUTER JOIN TABLE_B B
ON A.REF_NO = B.REF_NO
where B.DATE =
(SELECT MIN(DATE)
FROM TABLE_B C
WHERE B.REF_NO = C.REF_NO)
or not exists (select date from table_b b2 where b2.ref_no = a.ref_no)
Oct 28 '09 #3

P: 1
fgaudin, your answer is perfect. i had the same question and your solution solved it.
Jul 15 '11 #4

P: 1
You helped me as well, fgaudin. Thanks!
1 Week Ago #5

Post your reply

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