Join products table with prices table, for products with n valid prices

Question

I have the following tables of products and prices:

table: product

productID   
---------
1
2
3
4
5

table: price

priceID     productID   started     expires
-------------------------------------------
1           1           2011-11-05  NULL    
2           1           2011-11-05  2011-11-20
3           2           2011-11-05  NULL
4           3           2011-11-05  NULL
5           3           2011-11-06  2011-11-08

and I want to join them in such a way that:

1. Only one price per product
2. Prices are valid if price.started <= NOW() AND ( price.expires >= NOW() || price.expires IS NULL )
3. If more than one price is valid for one product, price with higher price.priceID should be chosen
4. If no price is valid, still show product info

These four criteria define the most valid (for luck of a better term) of valid prices. So for NOW() == 2011-11-09 the end result should be:

priceID     productID   started     expires
-------------------------------------------
2           1           2011-11-05  2011-11-20
3           2           2011-11-05  NULL
4           3           2011-11-05  NULL

I'm stuck in the

• If more than one price is valid for one product, price with higher price.priceID should be chosen

requirement, which stems from an outlandish business requirement for more than one valid prices for any given product for a period of time. My sql fu is extremely poor, I went as far as:

SELECT
    product.*,
    price.*
FROM
    product
LEFT JOIN
    price
ON 
    price.productID = product.productID
    AND price.started <= NOW() 
    AND (
        price.expires IS NULL
        OR price.expires >= NOW()
    )

Which of course for for NOW() == 2011-11-09 results in:

productID   priceID     productID   started     expires
-------------------------------------------------------
1           1           1           2011-11-05  NULL    
1           2           1           2011-11-05  2011-11-20
2           3           2           2011-11-05  NULL
3           4           3           2011-11-05  NULL

---

Clarifications (based on comments and answers):

• The requirements are uniquely weird, but valid. Overlapping intervals is an actual business requirement, sometimes I need to show all prices (easy) and sometimes just the one, the more recent (what I'm asking about).
• Can't do much to change the datamodel.
• I'm looking for the whole join, if possible. (or hints on how subqueries can be integrated into the final join)
• I'd prefer solutions based on standard SQL (ANSI / ISO) or MySQL.

Answer 1

SELECT
    p.*,
  ( SELECT 
        TOP 1 pr.price 
    FROM price pr 
    WHERE pr.productID = p.productID 
      AND pr.started <= NOW() 
      AND (  pr.expires IS NULL 
          OR pr.expires >= NOW()) 
    ORDER BY 
        pr.productID DESC
  ) AS price
FROM product p

Answer 2

Updated

The following (complete) query should do the trick:

SELECT
    product.*,
    price.*
FROM
    product, 
   (SELECT price.* FROM price 
        WHERE price.started <= NOW() 
        AND 
            (price.expires IS NULL
            OR price.expires >= NOW())
    GROUP BY price.productId
    BY price.priceId DESC LIMIT 1) AS `price`
WHERE price.productID = product.productID;

Answer 3

WITH pc AS (
    SELECT pc1.priceid, pc1.productid
    , pc1.started, pc1.expires
    FROM tmp.price pc1
    WHERE pc1.started <= now()
    AND (pc1.expires IS NULL OR pc1.expires > now() )
    AND NOT EXISTS ( SELECT *
        FROM tmp.price pcx
        WHERE pcx.productid = pc1.productid
        AND pcx.started <= now()
            AND (pcx.expires IS NULL OR pcx.expires > now() )
        AND pcx.priceid > pc1.priceid
        )
    )
SELECT pd.productid
    , pd.productname
    , pc.started
    , pc.expires
FROM pc
JOIN tmp.product pd ON pc.productid = pd.productid
    ;

A CTE is a nameless view. If your DBMS does not support CTEs, you could make the first (CTE) part of the query into a named view; the subsyntax is the same. YMMV.

Answer 4

SELECT
    pro.*
  , pri.*
FROM 
    product AS pro
  JOIN
    price AS pri
      ON pri.priceID =
         ( SELECT p.priceID
           FROM price p 
           WHERE p.productID = pro.productID
             AND p.started <= CURRENT_DATE() 
             AND ( p.expires IS NULL
                OR p.expires >= CURRENT_DATE()
                 )
           ORDER BY p.priceId DESC 
           LIMIT 1
         )

Answer 5

if you want to avoid subqueries, you may try the following statement:

SELECT
   product.*,
   price.*,
   MAX(price.priceID)
FROM
   product
LEFT JOIN
   price
ON 
   price.productID = product.productID
   AND price.started <= NOW() 
   AND (
      price.expires IS NULL
      OR price.expires >= NOW()
   )
GROUP BY price.productID

The GROUP BY will force only distinct products in the result with the max priceID. Hope it helps!