Hello all,
I'm sure that this situation is one of the most wondered questions with
JOIN clauses. Anyway, I couldn't find any clear information how to carry
out multiple joins in one query with proper results.
I have four tables:
1. Invoices
2. InvoiceContents
3. Customers
4. Payments
I try to get a list of all Invoices with total sum of the invoice and
paid sum of each invoices, as well as a customer name. I try following
query:
SELECT Invoices.*, SUM(InvoiceContents.Amount * InvoiceContents.Price)
AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal,
MAX(Payments.Date) LastPayment FROM Invoices LEFT JOIN InvoiceContents
ON (InvoiceContents.InvoiceID = Invoices.ID) LEFT JOIN Customers ON
(Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON
(Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID ORDER BY
Invoices.ID;
The query works fine, but multiples the total paid sum with the number
of the matched InvoiceContents. If I remove the JOIN with
InvoiceContents, the query works fine (except I can't get the total sum
of the invoice).
How should I do the join to get proper results?
Thanks,
Ville
