title: Get the lowest price for a product from a table of product prices per store
body: <p>I am making a website to compare product prices between different stores. I have created a search function where I want to display the current lowest price and this is where I am a bit stuck.</p>
  
  <p>I have a table <code>Products</code> with the basic information and a table <code>product_store</code> with the prizes in different stores for different products. Below is a basic schema of the database for these tables:</p>
  
  <pre><code>+-----------+   +---------------+
  | products  |   | product_store |
  +-----------+   +---------------+
  | id        |   | product_id    |
  | name      |   | store_id      |
  +-----------+   | price         |
                  | created_at    |
                  +---------------+
  </code></pre>
  
  <p>The <code>product_store</code> table has multiple prices for the same <code>product_id</code> and <code>store_id</code> so I can create a price history.</p>
  
  <p>Now I would like to create a query to get all products and their lowest price at the moment. So, you take the price for each store with the highest <code>created_at</code>, and from this collection I want to get the lowest value.</p>
  
  <p>This is what I have tried so far:</p>
  
  <pre><code>select products.*, prices.price 
  from products
  left join (
      SELECT p1.product_id, min(p1.price) as price, p1.created_at 
      FROM product_store p1 
      WHERE p1.created_at = (SELECT max(created_at) FROM product_store p2 WHERE p2.store_id = p1.store_id
  ) as prices
  on prices.product_id = products.id
  </code></pre>
  
  <p>I search for the highest <code>created_at</code> per store and product and get the lowest price for these rows. However this gives some very strange results where the prices get mixed up between the products and some products that have prices don't have any in the results.</p>
  
  <p>Can someone help me to create a good query for this problem?</p>
  
  <p>Thanks in advance. :)</p>