title: select min value of a field from joins table
body: <p>CREATE VIEW products_view
  AS</p>
  
  <p>Hi guys ! I've tree tables:</p>
  
  <pre><code>Products
  Categories
  Prices
  </code></pre>
  
  <p>A product belongs to one category and may has more prices. </p>
  
  <p>consider this set of data:</p>
  
  <p>Product : </p>
  
  <pre><code> id   title         featured    category_id 
   1   |    bread  |  yes      |    99
   2   |    milk   |  yes      |    99
   3   |    honey  |  yes      |    99
  </code></pre>
  
  <p>Price : </p>
  
  <pre><code> id   product_id    price        quantity
   1   |    1      |  99.99      |   10   
   2   |    1      |  150.00     |   50
   3   |    2      |  33.10      |   20
   4   |    2      |  10.00      |   11
  </code></pre>
  
  <p>I need to create a view, a <strong>full list of products</strong> that for each product select the min price and its own category. </p>
  
  <p>eg.</p>
  
  <pre><code> id       title      featured     cat.name       price      quantity
   1    |    bread   |  yes      |    food       | 99.99    |   10 
  </code></pre>
  
  <p>I tried the following query but in this way I select only the min Price.price value but Price.quantity, for example, came from another row. I should find the min Price.price value and so use the Price.quantity of this row as correct data.</p>
  
  <pre><code>CREATE VIEW products_view
  AS
  SELECT `Prod`.`id`, `Prod`.`title`, `Prod`.`featured`, `Cat`.`name`, MIN(`Price`.`price`) as price,`Price`.`quantity`
  FROM `products` AS `Prod`
  LEFT JOIN `prices` AS `Price` ON (`Price`.`product_id` = `Prod`.`id`) 
  LEFT JOIN `categories` AS `Cat` ON (`Prod`.`category_id` = `Cat`.`id`) 
  GROUP BY `Prod`.`id` 
  ORDER BY `Prod`.`id` ASC
  </code></pre>
  
  <p>My result is:</p>
  
  <pre><code> id     title        featured    cat.name       price        quantity
   1    |    bread   |  yes     |    food       | 99.99      |  **50**     &lt;-- wrong   
  </code></pre>
  
  <p>Can you help me ? Thx in advance !</p>