----- Original Message ----- From: "mysql" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
