From: Peter Brawley
Date: May 26 2006 1:42pm
Subject: Re: Joining multiple tables with grouping functions
List-Archive: http://lists.mysql.com/mysql/198324
Message-Id: <447705D9.5080107@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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