[thelist] mySQL queries
Wade Armstrong
wade_lists at runstrong.com
Mon Aug 12 14:31:01 CDT 2002
on 8/12/02 11:06 AM, Beau Hartshorne at beau at cubeinc.ca wrote:
> 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).
> 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.
How about:
(warning: didn't test)
SELECT
bi.invoice_id,
bi.job_id,
bj.job_id,
SUM(bj_amount) AS total_cost,
SUM(bi.amount) AS total_invoiced
FROM
billing_jobs bj
LEFT JOIN
billing_invoices bi
ON bj.job_id = bi.job_id
WHERE
(total_cost - total_invoiced) > 0
Wade
More information about the thelist
mailing list