Hi to all ..
I want to creat a View in my MySQL Database using this query:
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`clouddust`@`%` SQL SECURITY INVOKER VIEW `viewstocktransfersum` AS
select `tblstocktransfer`.`DateReference` AS `DateReference`,
`tblstocktransfer`.`InvoiceDate` AS `InvoiceDate`,
`tblstocktransfer`.`RefNo` AS `RefNo`,`tblstocktransfer`.`TransNo` AS `TransNo`,
`tblstocktransfer`.`WarehouseCodeFrom` AS `WarehouseCodeFrom`,
`tblstocktransfer`.`WHNameFrom` AS `WHNameFrom`,
`tblstocktransfer`.`WarehouseCodeTo` AS `WarehouseCodeTo`,`tblstocktransfer`.`WHNameTo` AS `WHNameTo`,
`tblproductlist`.`ProductCode` AS `ProductCode`,
`tblproductlist`.`Description` AS `Description`,
`tblproductlist`.`ProductCategory` AS `ProductCategory`,
`tblproductlist`.`UnitOfMeasure` AS `UnitOfMeasure`,
m.`Qty` AS Qty,
`tblstocktransfer`.`Status` AS `Status`,
`tblstocktransfer`.`StoreId` AS `StoreId`
from ((`tblstocktransfer`
INNER JOIN (SELECT TransNo, StoreId, ProductCode, SUM(tblstocktransferdetail.Qty) AS Qty
FROM `tblstocktransferdetail` GROUP BY StoreId, TransNo, ProductCode) m
on(`tblstocktransfer`.`TransNo` = `m`.`TransNo` AND tblstocktransfer.StoreId = m.StoreId)))
INNER JOIN tblproductlist ON (m.ProductCode = tblproductlist.ProductCode AND m.StoreId = tblproductlist.StoreId);
My problem is that this query returns an error "View's SELECT contains a sub query in the FROM clause". But when i run this query by not creating a view, it returns a correct result. Please help me through this.
Thank you all! God bless you always!!