StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoin products table with prices table, for products with n valid prices
text
Body
copied!<p>I have the following tables of products and prices:</p> <p><strong>table: product</strong></p> <pre><code>productID --------- 1 2 3 4 5 </code></pre> <p><strong>table: price</strong></p> <pre><code>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 </code></pre> <p>and I want to join them in such a way that: </p> <ol> <li>Only one price per product</li> <li>Prices are valid if <code>price.started <= NOW() AND ( price.expires >= NOW() || price.expires IS NULL )</code></li> <li>If more than one price is valid for one product, price with higher <code>price.priceID</code> should be chosen</li> <li>If no price is valid, still show product info</li> </ol> <p>These four criteria define the <em>most valid</em> (for luck of a better term) of valid prices. So for <code>NOW() == 2011-11-09</code> the end result should be: </p> <pre><code>priceID productID started expires ------------------------------------------- 2 1 2011-11-05 2011-11-20 3 2 2011-11-05 NULL 4 3 2011-11-05 NULL </code></pre> <p>I'm stuck in the </p> <ul> <li>If more than one price is valid for one product, price with higher <code>price.priceID</code> should be chosen</li> </ul> <p>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:</p> <pre><code>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() ) </code></pre> <p>Which of course for for <code>NOW() == 2011-11-09</code> results in: </p> <pre><code>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 </code></pre> <hr> <p><strong>Clarifications (based on comments and answers):</strong></p> <ul> <li>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).</li> <li>Can't do much to change the datamodel. </li> <li>I'm looking for the whole join, if possible. (or hints on how subqueries can be integrated into the final join)</li> <li>I'd prefer solutions based on standard SQL (ANSI / ISO) or MySQL. </li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload