List:General Discussion« Previous MessageNext Message »
From:mysql Date:April 18 2006 5:55am
Subject:My Left Joins are Doubling the SUM()
View as plain text  
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?
Thread
My Left Joins are Doubling the SUM()mysql18 Apr
  • Re: My Left Joins are Doubling the SUM()Martijn Tonies18 Apr
  • Re: My Left Joins are Doubling the SUM()Peter Brawley18 Apr
  • Re: My Left Joins are Doubling the SUM()Peter Brawley18 Apr