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

Question

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!

Answer 1

You have a few options:

1. Put the subquery into a view (could be slow, as the resulting view has no indexes on which to perform subsequent joins):

   CREATE VIEW mm AS
     SELECT   product_id, MIN(price_discount) price_discount
     FROM     product_items
     GROUP BY product_id
   ;
   
   CREATE VIEW my_view AS
     SELECT * FROM product_items m NATURAL JOIN mm
   ;
   

2. Use a correlated subquery (could also be slow, as the subquery must be evaluated for every record in the table - best performance will be attained with a composite index on (product_id, price_discount)):

   CREATE VIEW my_view AS
     SELECT * FROM product_items m WHERE price_discount = (
       SELECT MIN(mm.price_discount)
       FROM   product_items mm
       WHERE  mm.product_id = m.product_id
     )
   ;
   

3. Optimise the correlated subquery using the EXISTS strategy (will also benefit from a composite index on (product_id, price_discount)):

   CREATE VIEW my_view AS
     SELECT * FROM product_items m WHERE NOT EXISTS (
       SELECT 1
       FROM   product_items mm
       WHERE  mm.product_id     = m.product_id
          AND mm.price_discount < m.price_discount
       LIMIT  1
     )
   ;
   

Answer 2

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.
• ....