[thelist] mySQL queries

[Steve Lewis]

Beau Hartshorne wrote:

> 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.

> 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.)
Begin by exploring the syntax of Outer Joins.  You are currently
performing an implicit join which I would recommend against, and this
implicit join acts as an inner join which doesn't return what you really
want to find.

Making an outer join explicit will allow you to add an appropriate
condition to your WHERE clause such as 'OR COUNT(billing_invoice.amount)
= 0' or such.

> 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.)

Add another condition to your WHERE clause, being explicit with
parenthesis use to control order of operations, that excludes rows where
SUM(billing_invoice.amount) = billing_job.amount

> 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.)

One way to address this concern, while sacrificing minimal
normalization, would be to save after_invoice_balance_due as another
column in the invoice table.

A second way, which may make your query significantly easier to
conceptualize, but at the cost of a bit more normalization, would be to
store the current balance_due as another column in the job table
(instead or in addition to the above after_invoice_balance_due column).

> Will I have to resort to these hacks, assuming that I'm stuck with
> mySQL? Or is there some other way to deal with this?

MySQL is a very adequate database manager, and this sort of work can and
  should be done in the database as you know.  The trade-off in my
opinion of working with MySQL is that you are forced to be more careful
and explicit than when working with some other database managers (MS SQL
for example is 'easier' to work in sometimes), but this forces you to
learn the 'right way' to do things, which is valuable no matter what DB
you use.

Here is how I would start writting this query... I havn't actively
developed in MySQL for a year so please excuse if I am a bit rusty.

SELECT client.name as client_name, billing_job.job_id, billing_job.name,
billing_job.amount - SUM(billing_invoice.amount) AS balance_due
FROM billing_job INNER JOIN client
     ON billing_job.client_id = client.client_id
   LEFT OUTER JOIN billing_invoice
     ON billing_job.job_id = billing_invoice.job_id
WHERE billing_job.amount - SUM(billing_invoice.amount) > 0
GROUP BY billing_job.job_id, billing_job.name, billing_job.client_id

HTH

--Steve