Ville, >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. Try ... SELECT Invoices.*, ( SELECT SUM(Amount * .Price) FROM InvoiceContents WHERE InvoiceCOntents.InvoiceID = Invoices.ID ) AS InvoiceTotal, Customers.Name, SUM(Payments.Amount) PaidTotal, MAX(Payments.Date) LastPayment FROM Customers INNER JOIN Invoices ON (Customers.ID = Invoices.CustomerID) LEFT JOIN Payments ON (Payments.InvocieID = Invoices.ID) GROUP BY Invoices.ID There's an explanation at http://www.artfulsoftware.com/queries.php#4 PB ----- Ville Mattila wrote:
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
-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.7.1/347 - Release Date: 5/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
