help creating multi-table view

[aebstract]

CREATE VIEW totals AS
SELECT A1.ID SOID, SUM(A2.TOTALPRICE) TOTAL, SUM(A3.AMOUNT) PAYED
FROM SO A1, SOITEM A2, POSTRANSACTION A3
WHERE A1.ID = A2.SOID
AND A1.ID = A3.SOID
GROUP BY A1.ID

 

This creates a view that is close to what I need but not quite right. It is suppose to take all rows from the table soitem and add the totalprice column together as well as do the same thing under postransaction table with the amount column. The TOTAL column ends up being the correct amount, the total of all the lines where soitem.soid match so.id. The PAYED total ends up being a number of (TOTAL x # of rows in soitem where soitem.soid and so.id match). So a total of 732.07 from the lines (594.81 and 137.26) result in a PAYED amount of 1464.14. Here is the thing, for that order there is one line in postransaction with the correct soid and its total is 732.07 for column amount. That is problem 1, problem 2 is in my query and what's wrong is that I need to show the PAYED as 0.0 if there are no results in the postransaction table with the corresponding soid. Right now if there isn't a result in that table, it just doesn't add it to my view. Hope someone knows what I'm trying to do and it's an easy fix! 

[kickstart]

Hi

 

Your 2nd problem is because you are doing an inner join rather than an outer join.

 

Solution might be (not tested)

 

CREATE VIEW totals AS
SELECT A1.ID SOID, SUM(A2.TOTALPRICE) TOTAL, SUM(A3.AMOUNT) PAYED
FROM SO A1
LEFT OUTER JOIN (SELECT SOID, SUM(TOTALPRICE) AS totPrice FROM SOITEM GROUP BY SOID) A2
ON A1.ID = A2.SOID
LEFT OUTER JOIN (SELECT SOID, SUM(AMOUNT) AS totAmnt FROM POSTRANSACTION GROUP BY SOID) A3
ON A1.ID = A3.SOID

 

All the best

 

Keith

[aebstract]

Column unknown

A2.TOTALPRICE

At line 2, column 27

[kickstart]

Hi

 

Forgot to change the sum functions:-

 

CREATE VIEW totals AS
SELECT A1.ID SOID, totPrice AS TOTAL, totAmnt AS  PAYED
FROM SO A1
LEFT OUTER JOIN (SELECT SOID, SUM(TOTALPRICE) AS totPrice FROM SOITEM GROUP BY SOID) A2
ON A1.ID = A2.SOID
LEFT OUTER JOIN (SELECT SOID, SUM(AMOUNT) AS totAmnt FROM POSTRANSACTION GROUP BY SOID) A3
ON A1.ID = A3.SOID

 

All the best

 

Keith

[aebstract]

This is odd, but with that view created, when I try and pull information from that table I get this error:

 

Error code 335544352, SQL state HY000: GDS Exception. 335544352. no permission for read/select access to TABLE TOTALS2

Line 1, column 1

 

Execution finished after 0 s, 1 error(s) occurred.

 

Wondering if this is strictly a software issue or something that can be solved in the query? Also is there a way for me to change the view created so that blank spaces show up as 0.0?

 

 

Note: The error shows when I simply run "SELECT * FROM TOTALS2"

[kickstart]

Hi

 

You should be able to catch null values and output something more useful (use IFNULL).

 

As to the other issue, try running the SQL directly rather than as a view. Doubt it will make a difference but hoping it might give you a more useful error message

 

All the best

 

Keith

[aebstract]

So create an actual table instead of creating a view? The only reason I made a view was because the company said it would be safer to do it that way.

[kickstart]

Hi

 

No, both the view and a normal query read the table(s). The view is possibly safer and possibly easier for MySQL to optimise.

 

Just take the SELECT from the view and try running it directly (in phpmyadmin, or whatever MySQL tool you are using). Hopefully it might give you a more useful error.

 

All the best

 

Keith

[aebstract]

That doesn't give me an error, it shows the results. Which is why I was thinking it would be a software issue. Everything else seems to be working correctly, thanks!

[kickstart]

Hi

 

Afraid no more suggestions (I don't use views very often). I presume that TOTALS2 is a temporary table created by the view.

 

All the best

 

Keith

[aebstract]

Turned out that there was some sort of protection on these things. Had to run a "GRANT SELECT on totals2 to USER" to get access. Thanks for your help, hopefully I can get the ball rolling now.