CODEWITHSUNDEEP
mysql
Bharanikumar
Bharanikumar

Reputation: 25733

MYSQL view support subquery

Am just started reading view tutorial

i used this link view tutorial

in this tutorial they said

Subquery cannot be included in the SELECT statement.

but they gave one example that is ,

 CREATE VIEW vwProducts  AS 
 SELECT productCode,
        productName,
        buyPrice 
 FROM products
 WHERE buyPrice > (
      SELECT AVG  (buyPrice)
      FROM  products
 )
 ORDER BY buyPrice DESC

Tell me , is it possible ,

in view shall we use sub query or not ,

Upvotes: 0

Views: 7704

Answers (3)

sbrbot
sbrbot

Reputation: 6447

Generally SELECT statement can have SELECT subquery but contrary to other DBMS' MySQL's limitation is that one cannot create a view from such a select statement which contains subquery. This limitation can be easily overcome if one creates one view for subquery and then use this view for creation of originally wanted view.

Look at MySQL bug report: http://bugs.mysql.com/bug.php?id=16757

Upvotes: 1

joe
joe

Reputation: 768

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

Your example contains a subquery in the WHERE clause.

If you did have a subquery in the FROM clause, the workaround would be to make the subquery it's own view.

Upvotes: 7

ajreal
ajreal

Reputation: 47321

seems to be working in 5.0.27, what is your version?

A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns: -- http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Upvotes: 3

Related Questions