Re: replace subquery with left join
From: (wrong string) öth <dnoeth_at_gmx.de>
Date: Wed, 6 Nov 2002 22:08:21 +0100
Message-ID: <aqc06i$8qjue$1_at_ID-28204.news.dfncis.de>
Date: Wed, 6 Nov 2002 22:08:21 +0100
Message-ID: <aqc06i$8qjue$1_at_ID-28204.news.dfncis.de>
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 - 22:08:21 CET