0

I am getting below Error while creating view

View's SELECT contains a subquery in the FROM clause

please suggest an alternate option or method for this piece of code

Create or Replace view mydb.cnCustomerv
as
Select * from mydb.customermaster 
left join 
(
    Select customertransaction.transactioninvoiceno , customertransaction.transactionvalue , customertransaction.cardnumber from mydb.customertransaction order by customertransaction.transactionid desc limit 1
) as a on 
mydb.customermaster.cardnumber = a.cardnumber
left join
(
    Select customerredemption.transactionuniquecode , customerredemption.redemptionvalue , customerredemption.cardnumber from mydb.customerredemption order by customerredemption.redemptionid desc limit 1
) as B on
mydb.customermaster.cardnumber = B.cardnumber;
Nilesh Thakkar
  • 1,442
  • 4
  • 25
  • 36
Nikhil Krishnan
  • 51
  • 9
  • http://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation - this may help you, also check second answer by "Json on Linux Apache My" user who describes his problem and looking similar kind of problem. – Nilesh Thakkar Sep 18 '15 at 10:19
  • @Nilesh on creating view for sub query is not working i have already tried . – Nikhil Krishnan Sep 18 '15 at 10:29
  • yes creating view on sub query will not work but you can achieve it in other way, check my answer below. – Nilesh Thakkar Sep 18 '15 at 10:33

1 Answers1

0

According to the manual, VIEW cannot contain subquery. If you want to create a VIEW on your query, you need to create a separate views for your sub queries.

First View

Create or Replace VIEW mydb.CTView
AS 
Select ct.transactioninvoiceno, ct.transactionvalue, ct.cardnumber 
from mydb.customertransaction ct order by ct.transactionid desc limit 1

Second View

Create or Replace VIEW mydb.CRView
AS 
Select cr.transactionuniquecode, cr.redemptionvalue, cr.cardnumber 
from mydb.customerredemption cr order by cr.redemptionid desc limit 1

Main View,

Create or Replace view mydb.cnCustomerv
as
Select * from mydb.customermaster 
left join mydb.CTView as a on mydb.customermaster.cardnumber = a.cardnumber
left join mydb.CRView as B on mydb.customermaster.cardnumber = B.cardnumber;

A view definition is subject to the following restrictions: From MySQL Manual

  • The SELECT statement cannot contain a subquery in the FROM clause.
  • ....
Nilesh Thakkar
  • 1,442
  • 4
  • 25
  • 36