title: MySql retrieve products and prices
body: <p>I would like to retrieve a list of all the products, with their associated prices for a given period.</p>
  
  <p>The Product table:</p>
  
  <pre><code>Id
  Name
  Description
  </code></pre>
  
  <p>The Price table:</p>
  
  <pre><code>Id 
  Product_id
  Name
  Amount
  Start
  End
  Duration
  </code></pre>
  
  <p>The most important thing to not here, is that a Product can have mutliple prices, even over the same period, but not with the same duration. 
  For example, a price from "2013-06-01 -> 2013-06-08" and another from "2013-06-01 -> 2013-06-05"</p>
  
  <p>So my aim is to retrieve, for a given period, the lists of all products, paginated by 10 product for example, joined to the prices existant over the period.</p>
  
  <p>The basic way to do so would be:</p>
  
  <pre><code>SElECT *
  FROM product
  LEFT JOIN prices ON ...
  WHERE prices.start &gt;= XXX And prices.end &lt;= YYY
  LIMIT 0,10
  </code></pre>
  
  <p>The problem while using this simple solution, is that I can't retrieve only 10 Products, but 10 Products*Prices, which is not acceptable in my case.</p>
  
  <p>So the solution would be:</p>
  
  <pre><code>SElECT *
  FROM product
  LEFT JOIN prices ON ...
  WHERE prices.start &gt;= XXX And prices.end &lt;= YYY
  GROUP BY product.id
  LIMIT 0,10
  </code></pre>
  
  <p>But the problem here is, i'll only retrieve "1" price for each product.</p>
  
  <p>So I wonder what would be the best way to handle this.
  I could for example <strong>use a group function, like "group_concat"</strong>, and retrieve in a field all the prices in a string, like "200/300/100" and so on. That seem weird, and would need work on server-language side to transform to a readable information, but it could work.</p>
  
  <p>Another solution would be to <strong>use different column for each prices</strong>, depending on duration:</p>
  
  <pre><code>SELECT 
     IF( NOT ISNULL(price.start) AND price.duration = 1, price.amount , NULL) AS price_1_day
     ---- same here for all possible durations ---
  From ...
  </code></pre>
  
  <p>Thta would work too i guess (i'm not really sure if this is possible however), but I may need to create about 250 columns to cover all possibilities. <strong>Is that a safe option ?</strong></p>
  
  <p>Any help will be much appreciated </p>