Left Join with Sum
Posted: Wed Jan 17, 2007 5:27 pm
I have a slightly complicated join, which involves a sum and I can't get my head around exactly how it should be built.
There are two tables:
The first a list of invoices, with the usual info - date, amount, invoiceId, details, accountId etc
The second table is a list of allocations (receiptToInvoice table) where receipts assigned to invoices (either in full or part) - amount, invoiceId, receiptId
The query I want to return is a list of invoices which have amounts outstanding for a particular accountId.
I'm working with the following:
Any help gratefully received!!!!
Cheers
Nunners
There are two tables:
The first a list of invoices, with the usual info - date, amount, invoiceId, details, accountId etc
The second table is a list of allocations (receiptToInvoice table) where receipts assigned to invoices (either in full or part) - amount, invoiceId, receiptId
The query I want to return is a list of invoices which have amounts outstanding for a particular accountId.
I'm working with the following:
The error I get is a group use error - but I know the LEFT JOINED select is correct, with the GROUP, but I'm not sure exactly how it should fit in with the rest of the query.SELECT invoices.*, (invoices.amount - SUM(receipts.total_allocated)) as total_remaining FROM invoices LEFT JOIN (SELECT receiptToInvoice.invoiceId, sum(receiptToInvoice.amount) as total_allocated FROM receiptToInvoice GROUP BY receiptToInvoice.invoiceId) AS receipts ON receipts.invoiceId = invoices.invoiceId WHERE accountId='1' AND (invoices.amount - SUM(receipts.total_allocated))>0 ORDER BY invoiceDate ASC
Any help gratefully received!!!!
Cheers
Nunners