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