Hello,
I've spent a good amount of time trying to solve this problem, and I can't seem to figure it out. From what I've read in the MySQL documentation, it appears that what I'm trying to do is impossible, but I'd like to get it out there anyway to see if someone else has a solution.
I have two database tables. One consists of cases, and the other has comments for those cases. The structure is below (With all irrelevant fields removed):
CREATE TABLE `cases` (
`case_id` int(11) NOT NULL auto_increment,
`case_hours` double NOT NULL default '0',
`case_rate` double NOT NULL default '0',
`case_paid` double NOT NULL default '0',
PRIMARY KEY (`case_id`)
) TYPE=MyISAM ;
CREATE TABLE `comments` (
`comment_id` int(11) NOT NULL auto_increment,
`comment_case` int(11) NOT NULL default '0',
`comment_hours` double NOT NULL default '0',
PRIMARY KEY (`comment_id`)
) TYPE=MyISAM ;
The case table has 3 values in it used to calculate a balance: The number of hours spent on the case, the rate at which the client is being charged, and the total amount of money the client has paid thus far. You'll notice that the comment field also has a field for hours.
The balance is calculated as follows:
Balance = ( Hours in Case + Total Number of Comment Hours for Case ) * Rate - Amount Already Paid
What I'm trying to accomplish is a MySQL query that will automatically calculate the balance to that it can be easily sorted without having to do a bunch of PHP code. So far, this is the best I could come up with:
SELECT
cases.case_id ,
cases.case_hours ,
cases.case_rate ,
cases.case_paid ,
@commenttotal := SUM(IF(comments.comment_case = cases.case_id,comments.comment_hours,0)) AS comment_totalhours ,
@totalhours := (@commenttotal + cases.case_hours) AS case_totalhours ,
@cost := (@totalhours * cases.case_rate) AS case_cost ,
@balance := (@cost - cases.case_paid) AS case_balance
FROM
cases,
comments
GROUP BY
cases.case_id
I've run into two problems with this code:
1) @commenttotal returns a correct value in comment_totalhours, but is not included in @totalhours
2) According to the MySQL documentation:
Here's the catch... I'm running MySQL Version 4.0.26, so procedures aren't an option.