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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
