[thelist] mySQL queries

[Wade Armstrong]

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