List:General Discussion« Previous MessageNext Message »
From:Ville Mattila Date:May 26 2006 10:41am
Subject:Joining multiple tables with grouping functions
View as plain text  
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
Thread
Joining multiple tables with grouping functionsVille Mattila26 May
  • Re: Joining multiple tables with grouping functionsPeter Brawley26 May
  • Re: Joining multiple tables with grouping functionsJay Pipes26 May