Re: replace subquery with left join

Hans Sieghart wrote:
> 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, so I wrote:
>
> SELECT I.itemno, P.price
> FROM items AS I
> LEFT OUTER JOIN
> prices AS P
> ON I.itemno = P.itemno
> WHERE CONCAT(P.itemno, CAST(P.valdate AS CHAR(5))) IN
> (SELECT MAX(CONCAT(itemno, CAST(valdate AS CHAR(5))))
> FROM prices
> WHERE valdate <= (DAYS(CURDATE()) - 693594)
> GROUP BY itemno)

Try a multi-field subquery
 WHERE (P.itemno, P.valdate) IN

              (SELECT itemno, max(valdate)
                 FROM prices
                WHERE valdate <= (DAYS(CURDATE()) - 693594)
                GROUP BY itemno)

Dieter Received on Wed Nov 06 2002 - 15:08:21 CST