[thelist] mySQL queries

[Jay Blanchard]

[snip]
Table: billing_invoice
invoice_id
job_id
number
date
amount

Table: billing_job
job_id
client_id
name
amount
date
active

I want to display a list of jobs that have not been fully paid, and I
want the list to show how much is still owing on each job.

I've come up with a query that returns all of the jobs that have one or
more invoice, and that returns how much has been invoiced for these
jobs:

SELECT
	billing_invoice.*,
	billing_job.*,
	SUM(billing_invoice.amount)
FROM
	billing_invoice,billing_job
WHERE
	(billing_job.job_id = billing_invoice.job_id)
GROUP BY
	billing_job.job_id

1. It does not return jobs that have zero invoices. (I can hack around
this by running another query to find all the jobs without invoices.
Yuk.)

2. It will return jobs that have been fully invoiced. The SUM function
doesn't seem to work in the WHERE clause. (I can hack around this by
using PHP to ignore rows where the amount still owing is a positive
number. Yuk.)

3. All my worries would go away if each billing_job row kept its own
track of how much money had been paid and how much was owing. (Yuk. I'm
trying to do this properly.)
[/snip]

Try something like this; (some pseudo-code)

SELECT billing_job.*, billing_invoice.*, SUM(billing_invoice.amount) AS
total_invoice, SUM(billing_job.amount) AS total_job
FROM billing_job LEFT OUTER JOIN billing_invoice
ON (billing_job.id = billing_invoice.id)
GROUP BY billing_job
HAVING total_job > total_invoice


On second thought, it seems as if your tables lack the proper information or
you haven't given all of the information -- like which table is the base
table or parent table in this case, so this may not be correct. I hope that
this points you in the right direction though. Using a left join will show
jobs where no invoices exist. Where is the payment table(s)?

HTH!

Jay