Re: replace subquery with left join

On Thu, 31 Oct 2002 10:28:00 +0100, Hans Sieghart <Hans.Sieghart_at_frischeis.at.nospam> wrote:

>Imagine you have two tables:

<Mike, imagining . . .>

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

Hmmm. I'm going to pretend ValDate is a date.

CREATE TABLE Items (
  ItemNo INTEGER PRIMARY KEY
);

INSERT INTO Items VALUES (1);
INSERT INTO Items VALUES (2);
INSERT INTO Items VALUES (3);

CREATE TABLE Prices (
  ItemNo INTEGER NOT NULL,
  ValDate DATE NOT NULL,
  Price DECIMAL(10,2) NOT NULL,
  CONSTRAINT Prices_PK PRIMARY KEY (ItemNo, ValDate),   CONSTRAINT ItemNo_FK FOREIGN KEY (ItemNo) REFERENCES Items(ItemNo) );

INSERT INTO Prices VALUES (1, DATE '2002-01-01', 32.45);
INSERT INTO Prices VALUES (1, DATE '2002-01-15', 48.35);
INSERT INTO Prices VALUES (1, DATE '2002-01-31', 44.73);
INSERT INTO Prices VALUES (3, DATE '2002-02-15', 124.75);
INSERT INTO Prices VALUES (3, DATE '2007-02-15', 1124.75);

The prices having the latest date can be found by

SELECT P.ItemNo, P.Price
FROM Prices AS P
WHERE ValDate = (SELECT Max(ValDate)

                 FROM Prices 
                 WHERE (ItemNo = P.ItemNo)
                );

But you want the prices having the latest date on or before today.

SELECT P.ItemNo, P.Price
FROM Prices AS P
WHERE ValDate = (SELECT Max(ValDate)

                 FROM Prices 
                 WHERE (ItemNo = P.ItemNo) AND 
                       (ValDate <= CURRENT_DATE) 
                );

CURRENT_DATE is standard SQL.

Join the two tables like this. COALESCE() is standard SQL.

SELECT I.ItemNo, COALESCE(P.Price, 0.00) AS CurrentPrice FROM Items AS I
LEFT JOIN Prices AS P ON (P.ItemNo = I.ItemNo);

Now you can put the pieces together like this:

SELECT I.ItemNo, COALESCE(P.Price, 0.00) AS CurrentPrice FROM Items AS I
LEFT JOIN Prices AS P ON (P.ItemNo = I.ItemNo) WHERE (P.ValDate = (SELECT Max(ValDate)

                    FROM Prices 
                    WHERE (ItemNo = P.ItemNo) AND 
                          (ValDate <= CURRENT_DATE) 
                   )
      )
      OR
      (P.ValDate IS NULL);

     ITEMNO      CURRENTPRICE
=========== =================
          1             44.73
          2              0.00
          3            124.75

                  3 rows found

That last "IS NULL" clause lets items that have no prices join the party.

-- 
Mike Sherrill
Information Management Systems