459,212 Members | 1,370 Online 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
4 Replies

 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 