Re: replace subquery with left join

SELECT I.itemno, P.price
  FROM items AS I

       LEFT OUTER JOIN
       prices AS P
       ON I.itemno = P.itemno
 AND P.valdate= (SELECT MAX(P2.valdate)
                 FROM prices P2
                WHERE P2.valdate <= (DAYS(CURDATE()) - 693594)
                AND P2.itemno = P.itemno)

"Hans Sieghart" <Hans.Sieghart_at_frischeis.at.nospam> wrote in message news:pat1sukm5qti1kob3ru914isl6makddl1b_at_4ax.com...
> Hi NG,
>
> Imagine you have two tables:
>
> 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, 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)
>
> (This is DB2-Syntax; sorry, I don't know the correct ANSI-SQL92)
> (valdate is numeric and is the number of days since 1900-01-01)
>
> It works, but has 2 problems:
> 1. it is very, very slow
> 2. items, which don't have a price yet are not on the list (but should
> be)
>
> Can anybody help me with this?
>
> Kind regards
> Hans
>
Received on Sat Nov 02 2002 - 01:04:16 CET