Simple mySQL subqueries

Question

I have a simple problem, but I am new to SQL so please forgive my ignorance.

I have a accounting report that figures out balances oweing and adds up the balances to report to me what the total outstanding are within a specific period. The issue is for every JOB there are many invoices that provide a running total/balance, because of this when my current query adds up the balances it shows me outstanding amounts that are sky high, we have found that the current code is adding the balances of all the invoices. Example- If JOB ID 001 has four invoices- I-001 balance 200, I-002 balance 100, I-003 balance 50, I-004 balance 0.

It will show me that there is $350 outstanding when in fact it is zero.

The solution that I can think of(which I am not sure how to code) are to group the results by job ID and use the MAX feature to select only the higest ID for every JOBID

The problem I am having is that the balances are not saved to the table but recalculated every time they are needed. What can you suggest to show me only the balance from the highest Invoice ID for a particular JOBID My invoice table has the following columns:

1   ID  int(11)
2   ParentID    int(11)
3   JOBID   varchar(100)
4   DATE    date
5   LENSES  decimal(10,2)
6   FRAMES  decimal(10,2)
7   TAXABLEGOODS    decimal(10,2)
8   DISCOUNT    decimal(10,2)
9   PREVIOUSBALANCE     decimal(10,2)
10  PAYMENT     decimal(10,2)
11  PAYMENTTYPE     varchar(200)
12  NOTES   varchar(255)
13  PMA     decimal(10,2)

The current code looks like this:

$pieces = explode("-", $_REQUEST["STARTDATE"]);
$startDate=$pieces[2] . "-" . $pieces[1] . "-" . $pieces[0];



if($_REQUEST["ENDDATE"]==""){
$endDate=0;
}else{
$pieces = explode("-", $_REQUEST["ENDDATE"]);
$endDate = $pieces[2] . "-" . $pieces[1] . "-" . $pieces[0];
}

$result     = mysql_query("SELECT * FROM INVOICES WHERE DATE BETWEEN '" .     $startDate . "' AND '" . $endDate . "'");
$totalCount = 0;
$total      = 0;
$allPayments= 0;
$pmtTypes   = Array();
$totalHST   = 0;
$outstanding=0;
$payments=0;

while($theRow=mysql_fetch_array($result)){

$allPayments += $theRow["PAYMENT"];

if($theRow["PAYMENTTYPE"] == "") $theRow["PAYMENTTYPE"] = "BLANK";

if(isset($pmtTypes[$theRow["PAYMENTTYPE"]])){
    $pmtTypes[$theRow["PAYMENTTYPE"]] += $theRow["PAYMENT"];;
}else{
    $pmtTypes[$theRow["PAYMENTTYPE"]] =  $theRow["PAYMENT"];;
}

if($theRow["PREVIOUSBALANCE"] != 0) continue;

$subTotal = ( ( $theRow["LENSES"] + $theRow["FRAMES"] + $theRow["TAXABLEGOODS"] )  - $theRow["DISCOUNT"]);
$HST      = ( $theRow["TAXABLEGOODS"] * 0.13 );
$totalHST+= $HST;
$total   += ( $subTotal + $HST );
$payments+=$theRow["PAYMENT"];

}
$outstanding=$total-$payments;

Anyone have anything to contribute? I would appreciate any help.

You could use some indentation in your php code for readability — abcde123483, Nov 17 '11 at 21:22
Thanks, when I get it working I will spend sometime cleaning up the look. Any thoughts on a solution to the problem? — user1052646, Nov 17 '11 at 21:36

score 0 · Answer 1 · edited May 23 '17 at 12:29

show me only the balance from the highest Invoice ID for a particular JOBID

For a single job ID:

SELECT lenses+frames+taxablegoods-discount+previousbalance AS balance
FROM invoices WHERE jobid=?
ORDER BY id DESC LIMIT 1

group the results by job ID and use the MAX feature to select only the higest ID for every JOBID

If you want to query the latest invoice for many jobs at once, you are talking about a per-group-maximum selection. SQL doesn't make this as easy to do as you'd hope. There are various approaches, including subqueries, but on MySQL I typically favour the null-self-join:

SELECT i0.jobid, i0.lenses+...etc... AS balance
FROM invoices AS i0 LEFT JOIN invoices AS i1 ON i1.jobib=i0.jobid AND i1.id>i0.id
WHERE i1.id IS NULL

That is: “give me rows where there is no row with the same job ID but a higher invoice ID”.

If doing this between two dates, you'd need to apply the condition to both sides of the join:

SELECT i0.jobid, i0.lenses+...etc... AS balance
FROM invoices AS i0 LEFT JOIN invoices AS i1 ON
    i1.jobib=i0.jobid AND i1.id>i0.id AND
    i1.date BETWEEN ? AND ?
WHERE
    i0.date BETWEEN ? AND ?
    i1.id IS NULL

Incidentally you have an SQL-injection vulnerability from putting strings into your query. Use mysql_real_escape_string() or, better, parameterised queries to avoid these problems.

score 0 · Answer 2 · answered Nov 17 '11 at 21:38

The subquery would look something like this:

SELECT * FROM INVOICES I1
WHERE DATE BETWEEN ? AND ?
AND ID = (SELECT MAX(ID) FROM INVOICES I2
          WHERE DATE BETWEEN ? AND ?
          AND I2.JOBID = I1.JOBID)

You should look into using parameterised queries, instead of concatenating the string with user input. At a bare minimum use mysql_real_escape_string - see here: http://www.php.net/manual/en/function.mysql-real-escape-string.php

Simple mySQL subqueries

2 Answers2