error in "view" creation
Posted: 12. January 2011 11:00
I tried to create a view in xampp
CREATE VIEW CompanyTotalCost
AS
SELECT c.Name,
COALESCE(p.TotalCost, 0)
+ COALESCE(s.TotalCost, 0) AS TotalCost
FROM Companies AS c
LEFT JOIN (SELECT CompID, SUM(Cost) AS TotalCost
FROM Products
GROUP BY CompID) AS p
ON p.CompID = c.CompID
LEFT JOIN (SELECT CompID, SUM(Cost) AS TotalCost
FROM Services
GROUP BY CompID) AS s
ON s.CompID = c.CompID
my database is a simple database with 3 tables product(name, cost, id, CompID) services(name, cost, id, CompID) and company(name, id, totalcost)
the xampp appears an error: #1349 - View's SELECT contains a subquery in the FROM clause.
does anyone has any idea about this????
thanks a lot
Manolis
CREATE VIEW CompanyTotalCost
AS
SELECT c.Name,
COALESCE(p.TotalCost, 0)
+ COALESCE(s.TotalCost, 0) AS TotalCost
FROM Companies AS c
LEFT JOIN (SELECT CompID, SUM(Cost) AS TotalCost
FROM Products
GROUP BY CompID) AS p
ON p.CompID = c.CompID
LEFT JOIN (SELECT CompID, SUM(Cost) AS TotalCost
FROM Services
GROUP BY CompID) AS s
ON s.CompID = c.CompID
my database is a simple database with 3 tables product(name, cost, id, CompID) services(name, cost, id, CompID) and company(name, id, totalcost)
the xampp appears an error: #1349 - View's SELECT contains a subquery in the FROM clause.
does anyone has any idea about this????
thanks a lot
Manolis