it--solutions
Posts: 24
|
| Posted: 12/08/2009, 3:43 AM |
|
Hello all,
What a learning curve i've been on since using codecharge. 
My current issue is one of some mysql syntax.
I have created a grid left joining two tables on the common field 'invoice_number'
the first table is called 'invoices'
| invoiceID | invioce_number | invoice_date | total |
| 1 | 20091206-142527 | 2009-12-06 | 93.5 |
| 2 | 20091208-212731 | 2009-12-08 | 125 |
the second table is called receipts
| receiptID | receipt_date | invoice_number | payment_amount |
| 1 | 2009-12-07 | 20091206-142527 | 50 |
| 2 | 2009-12-08 | 20091206-142527 | 25 |
| 3 | 2009-12-08 | 20091208-212731 | 110 |
| 4 | 2009-12-08 | 20091208-212731 | 15 |
in codecharge i am attempting to only show invoices that have not been fully paid. ie accounts receivable
this is the syntax i have succeeded to start with:
SELECT invoices.*, sum(receipts.payment_amount) as paid FROM receipts RIGHT JOIN invoices ON receipts.invoice_number = invoices.invoice_number group by receipts.invoice_number
The result is...
| invoiceID | invoice_number | invoice_date | total | paid |
| 1 | 20091206-142527 | 2009-12-06 | 93.5 | 75 |
| 2 | 20091208-212731 | 2009-12-08 | 125 | 125 |
What i am trying to eliminate from the grid is the invoices that have had full payment, ie.
invoices.total = sum(receipts.payment_amount) where receipts.invoice_number = invoices.invoice_number
any ideas?
|
 |
 |
datadoit
|
| Posted: 12/08/2009, 6:26 AM |
|
Use a sub-select. Something like:
SELECT invoices.* FROM whatever RIGHT JOIN yada yada WHERE
invoices.invoice_total <> (SELECT SUM(payment_amount) AS total_paid FROM
receipts WHERE invoices.invoice_number = receipts.invoice_number)
or, as I would do, create a view that holds the sum'ed up values from
receipts at all times and refer to that. It'll make things quicker and
easier to reference at any other time.
|
|
|
|
|
