left outer join with subselect

[johnfaulkner]

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.

[frozenmist]

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

[fgaudin]

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)

[larry wolf]

fgaudin, your answer is perfect. i had the same question and your solution solved it.

[mgperson]

You helped me as well, fgaudin. Thanks!