mysql,
> 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
That why God invented correlated subqueries :-). Try something like ...
SELECT
packageItemID,
SUM(packageItemPrice),
( SELECT SUM(c.packageCreditAmount)
FROM packageCredit c
WHERE c.packageCreditItemID = packageItemID
) AS CreditSum,
( SELECT SUM(t.packageItemTaxAmount)
FROM packageItemTax t
WHERE t.packageItemTaxItemID = packageItemID
) AS TaxSum
FROM packageItem
PB
-----
> 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?
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.3/317 - Release Date: 4/18/2006
