Geek Answers Handbook

MYSQL Get the record with the lowest value | The SELECT view contains a subquery in the FROM clause.

I am working on this request and it makes me go blank.

I have a product table and a table with additional products. In short, I want to create a presentation with product data and the lowest (discount) price of offal. (Think of a shirt with several offal (colors / sizes), etc.)

Secondly, I want to use this query in VIEW, and this part annoys me.

Now I have a request:

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 works and I get good results. But now I want to create a view (vw_product_lowest). And then the error: ERROR 1349 (HY000): View SELECT contains a subquery in the FROM clause

Can someone help me convert this request to a compatible VIEW request? Thanks!

+4
source share
2 answers

According to the manual, VIEW cannot contain a subquery. If you really want to create VIEW in your request, 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 request 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 reference system or user variables.
  • In a saved program, the definition cannot refer to program parameters or local variables.
  • ....
+6
source

You have several options:

  • Put the subquery in the view (it can be slow since the resulting view has no indexes 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 ;

  • Use a correlated subquery (it can also be slow, since the subquery must be evaluated for each record in the table - better performance will be achieved 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 ) ;

  • Optimize the correlated subquery with the EXISTS strategy (a composite index at (product_id, price_discount) will also be useful):

     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 ) ;
+9
source

More articles:


All Articles