title: MYSQL Get record with lowest value | View's SELECT contains a subquery in the FROM clause
body: <p>I have been working on this query and it is driving me nuts.</p>
  
  <p>I have a product table, and a table with sub products.
  In short I want to create a view with the product data, and the lowest (discount) price of the sub products. (Think about a shirt, with several sub product (colors/sizes) etc)</p>
  
  <p>Secondly I want to use this query in a VIEW and this part is driving me nuts.</p>
  
  <p>The query I have now: </p>
  
  <pre><code>  SELECT m.* from product_items m join
    (select product_id, min(price_discount) md 
         from product_items group by product_id) mm
    on m.product_id=mm.product_id and m.price_discount=md
  </code></pre>
  
  <p>This query is working and I get good results. But now I want to create a view (vw_product_lowest).
  And then the error: <code>ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause</code></p>
  
  <p>Can anyone help me to transform that query to a compatible VIEW query? Thanks!</p>