Joining invoices and payments - seems like a simple problem

Here's a challenge. In the accounts system I've developed I'd like to get a list of outstanding invoices.

I'm trying to do this with three linked tables:

The invoices table has fields including invoicenumber, date, order, customer etc
The invoiceitems table has fields including iteminvoicenumber, description, itemamount etc.
The payments table has fields including paymentinvoicenumber, paymentref, paymentamount etc.

The three tables are joined on the following fields: invoicenumber - iteminvoicenumber - paymentinvoicenumber

For each invoice there may be several invoiceitems and several payments

I'd first like to produce a list which shows the balance of each invoice.

Here's my code which doesn't quite work.

select sum(itemamount)-sum(paymentamount) as invoicebalance, invoicedate, invoicenumber
from invoices
join invoiceitems
on iteminvoicenumber=invoicenumber
join payments
on paymentinvoicenumber=invoicenumber
group by invoicenumber
order by invoicenumber

The problem with this is that if there is more than one invoice item the payment is duplicated for each. I'm not sure if this can be solved by using left/right/inner/outer joins or if something more is needed.

Any help gratefully received!

Thanks

here's how to do it by using subqueries to aggregate both the item amounts and the payment amounts

simple, yes?

Hi Rudy

Thanks for your swift and brilliantly simple reply!

Hi Rudy

Thanks for your swift and brilliantly simple reply! There are several principles in this which are new to me so it is also a useful learning opportunity for me.

One problem - it only lists the invoices with zero balances (paid) or those that are partially paid. ie if there is no payment record against an invoice then that invoice is not listed. I am rather more interested in the invoices which have a non-zero balance!

This could be my fault as I had simplified my example to demonstrate the problem and had changed some of the field names to make them more intuitive.

Here is the actual code I ended up with thanks to you, which runs with no errors:

SELECT i.invoiceid
, i.invoicedate
, ia.sum_itemamounts - p.sum_paymentamounts AS invoicebalance
FROM sa_invoices AS i
INNER
JOIN ( SELECT trinvoiceid
, SUM(tramt+(trvatrate/100*tramt)) AS sum_itemamounts
FROM sa_invoiceitems
GROUP
BY trinvoiceid ) AS ia
ON ia.trinvoiceid = i.invoiceid
INNER
JOIN ( SELECT pinvoiceid
, SUM(pamt) AS sum_paymentamounts
FROM sa_payments
GROUP
BY pinvoiceid ) AS p
ON p.pinvoiceid = i.invoiceid
ORDER
BY i.invoiceid

I should have said in my original post: For each invoice there may be several invoiceitems and several payments, or no payments.


One slight further refinement, if we can get this to list every invoice balance, will be to leave out the zero balance ones, ie the invoices which have been paid. Hopefully I can add somewhere:

WHERE invoicebalance <> 0

??

Many thanks for your help so far.

David

here you go, sir --

Hi Rudy

That works perfectly! This is the first time in 20 years I've ever asked for help on a forum - I always try hard to work things out for myself, but this problem had eluded me for months.

Thanks very much!

Best wishes

David

Usenet aside, the earliest web-based forums date from the mid-1990s, so let's call it 18 years.

I'll get my coat...

:-)