Re: replace subquery with left join

On Thu, 31 Oct 2002 16:29:45 +0100, "Anders Altberg" <pragma_at_telia.com> wrote:

>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).

I had to rewrite that a bit as

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 CURRENT_DATE) GROUP BY I.itemno;

I'm not sure that's what you meant. IAC, it gives the wrong answer with the data I used against Mimer SQL. See my reply to the OP in this thread.

-- 
Mike Sherrill
Information Management Systems