[SOLVED] Variables, Math, and Sorting problem.

[r4cc00n]

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:

 

Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list.

 

    Here's the catch... I'm running MySQL Version 4.0.26, so procedures aren't an option.

 

[Wildbug]

SELECT
cases.case_id,
cases.case_hours + SUM(comments.comment_hours) AS TotalHours,
(cases.case_hours + SUM(comments.comment_hours)) * cases.case_rate AS Cost,
cases.case_paid AS Paid,
((cases.case_hours + SUM(comments.comment_hours)) * cases.case_rate) - cases.case_paid AS Balance
FROM cases JOIN comments ON case_id=comment_case
GROUP BY cases.case_id

[r4cc00n]

SELECT
cases.case_id,
cases.case_hours + SUM(comments.comment_hours) AS TotalHours,
(cases.case_hours + SUM(comments.comment_hours)) * cases.case_rate AS Cost,
cases.case_paid AS Paid,
((cases.case_hours + SUM(comments.comment_hours)) * cases.case_rate) - cases.case_paid AS Balance
FROM cases JOIN comments ON case_id=comment_case
GROUP BY cases.case_id

 

Thanks, Wildbug, for the suggestion. There is one problem with this method, however;  It does not return cases that do not have comments associated with them.

[Wildbug]

Oh.  Right.  Try this instead:

 

SELECT
cases.case_id,
cases.case_hours + SUM(IFNULL(comments.comment_hours,0)) AS TotalHours,
(cases.case_hours + SUM(IFNULL(comments.comment_hours,0))) * cases.case_rate AS Cost,
cases.case_paid AS Paid,
((cases.case_hours + SUM(IFNULL(comments.comment_hours,0))) * cases.case_rate) - cases.case_paid AS Balance
FROM cases LEFT JOIN comments ON case_id=comment_case
GROUP BY cases.case_id

[r4cc00n]

Oh.  Right.  Try this instead:

 

SELECT
cases.case_id,
cases.case_hours + SUM(IFNULL(comments.comment_hours,0)) AS TotalHours,
(cases.case_hours + SUM(IFNULL(comments.comment_hours,0))) * cases.case_rate AS Cost,
cases.case_paid AS Paid,
((cases.case_hours + SUM(IFNULL(comments.comment_hours,0))) * cases.case_rate) - cases.case_paid AS Balance
FROM cases LEFT JOIN comments ON case_id=comment_case
GROUP BY cases.case_id

 

Worked perfectly.  Thank you for your help