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