replace subquery with left join

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 - 10:28:00 CET