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)
Leave a comment: