Re: replace subquery with left join

Hans Sieghart <Hans.Sieghart_at_frischeis.at.nospam> wrote:
> Table I: item base segment
> Table P: prices
>
> In table P are the prices and a date from which the prices are valid.
> It is possible to put records with futuredates in that table. Also
> there are all old prices stored.
>
> Now I want to get a price-list

here is my solution - it is ugly and untested, maybe it is wrong, but it could help you to find a solution.

if i did it correct, it should be MUCH faster that yours. never use string operations!

select i.itemno, p.price, p.valdate
from items i
left outer join (
  select itemno, price, valdate
  from prices p
  where valdate = (
    select max(valdate) from prices where (itemno=p.itemno) and (valdate <= curdate())
  )
) p on i.itemno = p.itemno

--
Moritz Franckenstein
mailto:maf-soft_at_gmx.net
http://www.maf-soft.de/
icq: 22030984  y!: maf_soft