January 17, 2003 at 9:42 am
Here's another one for the Gurus out there.
These two queries get two different numbers. I don't understand why - are they not two different ways of accomplishing the same thing?
/*********************** Query 1 ***********************/
Select sum(t.amount)
from cptrans t inner join cptrans_blacklist c on t.recordid = c.recordid
Results: -783682.7500
/*********************** Query 2 ***********************/
Select sum(t.amount)
from cptrans t where t.recordid in (select distinct recordid from cptrans_blacklist)
Results: -798 876.40
The cptrans_blacklist table has a bunch of IDs in it that didn't pass the test. In both cases all tables are the same and its in the same db.
Chris.
Chris.
January 17, 2003 at 9:49 am
These queries are not the same. My gamble would be that you have duplicate recordid's in the cptrans_blacklist.
If you join with the table, the record from cptrans will be duplicated.
To yield the same result, change query 1 to :
Select sum(t.amount)
from cptrans t inner join (select distinct reocrdid from cptrans_blacklist) c on t.recordid = c.recordid
January 17, 2003 at 9:53 am
D'OH!
Right, there are duplicate records in there, probably causing their amount to be repeated and summed again.
Thanks!
Chris.
quote:
These queries are not the same. My gamble would be that you have duplicate recordid's in the cptrans_blacklist.If you join with the table, the record from cptrans will be duplicated.
To yield the same result, change query 1 to :
Select sum(t.amount)
from cptrans t inner join (select distinct reocrdid from cptrans_blacklist) c on t.recordid = c.recordid
Chris.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply