469,954 Members | 1,795 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,954 developers. It's quick & easy.

left outer join with subselect

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
4 21432
frozenmist
179 Expert 100+
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
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
fgaudin, your answer is perfect. i had the same question and your solution solved it.
Jul 15 '11 #4
You helped me as well, fgaudin. Thanks!
Jul 23 '20 #5

Post your reply

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

Similar topics

3 posts views Thread by Ian Boyd | last post: by
2 posts views Thread by Morten K. Poulsen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.