MYSQL Get record with lowest value | View's SELECT contains a subquery in the FROM clause

mysql, sql, view

Solution

According to the manual, `VIEW` cannot contain subquery. If you really want to create a `VIEW` on your query, you need to create a separate view for your subquery, ex

First VIEW

CREATE VIEW MinimumPrice
AS 
SELECT  product_id, MIN(price_discount) md 
FROM    product_items 
GROUP   BY product_id

Second VIEW

CREATE VIEW MinimumPriceList
AS 
SELECT  m.* 
FROM    product_items m 
        INNER JOIN MinimumPrice mm 
            ON  m.product_id = mm.product_id AND 
                m.price_discount = mm.md

To query the MAIN VIEW,

SELECT * FROM MinimumPriceList

A view definition is subject to the following restrictions: FROM MySQL MANUAL

- The SELECT statement cannot contain a subquery in the FROM clause.

- The SELECT statement cannot refer to system or user variables.

- Within a stored program, the definition cannot refer to program parameters or local variables.

- ....

Problem

I have been working on this query and it is driving me nuts. 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) Secondly I want to use this query in a VIEW and this part is driving me nuts. The query I have now: ``` 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 ``` This query is working and I get good results. But now I want to create a view (vw_product_lowest). And then the error: `ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause` Can anyone help me to transform that query to a compatible VIEW query? Thanks!

Original source