[thelist] mySQL queries

[Jonathan_A_McPherson at rl.gov]

Beau,

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

To do this, you'll need to use an outer join. I'm not certain what the
syntax is for that in MySQL, but in Microsoft's SQL, it'd be something like
this:

old -
FROM
	billing_invoice,billing_job
WHERE
	(billing_job.job_id = billing_invoice.job_id)

new -
FROM
	billint_invoice
	LEFT OUTER JOIN billing_job
	ON billing_job.job_id = billing_invoice.job_id

Essentially, you're saying "Return all the records from billing_job (the
'left' or first table), even if they don't have any matching records in
billing_invoice." The fields that would come from billing_invoice will be
NULL on the unmatched records.

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

That's because SUM is an aggregate function, and those aren't allowed in the
WHERE clause. They *are*, however, allowed in the "HAVING" clause. Add a
HAVING clause to your query and put your SUM criteria there.

(Again, I use Microsoft SQL here at work -- YMMV.)

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

Bravo! (-:

--
Jonathan McPherson, LMIT/SD&I
Software Engineer & Web Systems Analyst
email / jonathan_a_mcpherson at rl dot gov


-----Original Message-----
From: Beau Hartshorne [mailto:beau at cubeinc.ca]
Sent: Monday, August 12, 2002 11:07 AM
To: 'thelist'
Subject: [thelist] mySQL queries


Hi,

I'm building an online time/billing application for myself with php/mysql.
I've set up a billing_job table, and a billing_invoice table. Each job can
have one or more invoices associated with it (deposit, milestones, final).

The tables are set up something like this:

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

There are a couple problems with the query (aside from the .*'s, I'll fix
those later):

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

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?

Thanks,

Beau


--
For unsubscribe and other options, including
the Tip Harvester and archive of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !