Re: Subquery or join

[Peter Brawley]

Something like this?

select 
  m.key_id, m.key_desc, m.Qty, 
  d.updfirst, f.upd_by as updfirstby, 
  d.updlast, l.upd_by as updlastby
from  main m
join (
  select key_id, min(upd_date) updfirst, max(upd_date) as updlast
  from trans1
  group by key_id
) d on m.key_id=d.key_id
join (
  select key_id, upd_date, upd_by
  from main
) f on d.key_id=f.key_id and d.updfirst=f.upd_date
join (
  select key_id, upd_date, upd_by
  from main
) l on d.key_id=l.key_id and d.updlast=l.upd_date

If that's not right, let's see the DDL and enough sample data to show what's wrong.