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
