Re: replace subquery with left join

Hans

Given Items
1
2
3
and Prices

   Itemno      Price         Valdate
          1         11,00        2002/11/10 00:00:00
          1         10,00        202/10/21 00:00:00
          2         20,00       2002/10/21 00:00:00
           2        22,00       2002/10/26 00:00:00
           2        23,00      2002/11/05 00:00:00

rows 2 and 4 are sought + a row for item 3

SELECT I.itemno, MAX(P2.price), MAX(P2.valdate) ;  FROM Items I LEFT JOIN Prices P1 ON I.itemno=P1.itemno;  LEFT JOIN Prices P2 ON P1.itemno=P2.itemno AND P1.valdate BETWEEN P2.valdate AND CURDATE) ;
 GROUP BY I.itemno

returns

        Itemno      Max_price             Max_valdate
          1               10,0000         2002/10/21 00:00:00
          2               22,0000         2002/10/26 00:00:00
          3 .            NULL.                .NULL.

But I'm a bit leery against MAX(price).

"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 Thu Oct 31 2002 - 16:29:45 CET