From: Martijn Tonies
Date: April 18 2006 12:41pm
Subject: Re: My Left Joins are Doubling the SUM()
List-Archive: http://lists.mysql.com/mysql/196986
Message-Id: <02a901c662e5$63c6e5d0$cd02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: 7bit


----- Original Message ----- 
From: "mysql" <mysql@stripped>
To: <mysql@stripped>
Sent: Tuesday, April 18, 2006 7:55 AM
Subject: My Left Joins are Doubling the SUM()


> My Left Joins are Doubling the SUM()
>
> SELECT packageItemID, packageItemName,packageItemPrice
> ,SUM(packageItemTaxAmount) as packageItemTaxAmount
> ,SUM(packageCreditAmount) as packageCreditAmount
> FROM packageItem
> LEFT JOIN packageCredit ON packageItemID=packageCreditItemID
> LEFT JOIN packageItemTax ON packageItemTaxItemID=packageItemID AND
> packageItemTaxActive=1
> GROUP BY packageItemID
> ORDER BY packageItemID
>
> packageItem Table
> packageItemID | packageItemName | packageItmePrice
> 1 | Delta Hotel | 100.00
>
> packageCredit Table
> packageCreditID | packageCreditItemID | packageItemType |
> packageCreditAmount
> 1 | 1 | Deposit | 25.00
> 2 | 1 | Balance | 92.00
>
> packageItemTax
> packageItemTaxID | packageItemTaxItemID | packageItemTaxName |
> packageItemTaxAmount
> 1 | 1 | GST | 7.00
> 2 | 1 | HST | 10.00
>
> The desired result of the query should be:
>
> 1 | Delta Hotel | 100.00 | 17.00 | 117.00
>
> But it keeps doubling the tax and the credit amounts and results look like
> this
>
> 1 | Delta Hotel | 100.00 | 34.00 | 234.00
>
> is there a way to execute this query without this happening?

Start by supplying sample data from which you are trying to
get the wanted result.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com