Are these 2 queries functionally equivalent? 1

[kaht]

As the title implies:

[COLOR=blue]select[/color] sum([COLOR=blue]t[/color].TOTAL) recordTotal
[COLOR=blue]from[/color] TRANSFERS..REPTRANSF [COLOR=blue]t[/color]
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] TRANSFERS..REPHIERARCHY h
   [COLOR=blue]on[/color] h.VP_ID = [COLOR=red]'ES3891'[/color]
      and h.AGENT_UUID = [COLOR=blue]t[/color].AGENT_UUID
[COLOR=blue]where[/color] [COLOR=blue]t[/color].COL_DAT = [COLOR=red]'2008-03-01'[/color]

 
[COLOR=blue]select[/color] sum(TOTAL) recordTotal
[COLOR=blue]from[/color] TRANSFERS..REPTRANSF
[COLOR=blue]where[/color] COL_DAT = [COLOR=red]'2008-03-01'[/color]
   and AGENT_UUID in (
      [COLOR=blue]select[/color] AGENT_UUID 
      [COLOR=blue]from[/color] TRANSFERS..REPHIERARCHY
      [COLOR=blue]where[/color] VP_ID = [COLOR=red]'ES3891'[/color]
   )

These 2 queries are returning different numbers. To me, they seem to basically be the exact same query. I looked at it for a good 20 mins yesterday and could not figured out why one would return more results than the other.

(the one with the inner join was returning slightly more rows)

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[SQLSister]

have you tried

select sum(t.TOTAL) recordTotal
from TRANSFERS..REPTRANSF t
   inner join TRANSFERS..REPHIERARCHY h
   on 
       h.AGENT_UUID = t.AGENT_UUID
where t.COL_DAT = '2008-03-01' and h.VP_ID = 'ES3891'

Sometimes I've run into an issue in inner joins where putting a condition in the join doesn't give the same results as putting it in the where.


"NOTHING is more important in a database than integrity." ESquared

 

[kaht]

Sometimes I've run into an issue in inner joins where putting a condition in the join doesn't give the same results as putting it in the where.

SQLSister, do you know what the cause of this would be? It would be nice to be able to identify this situation in the future and fix it before it becomes problematic.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[SQLSister]

No I'm not really sure, I just know it doesn't happen all the time but sometimes I've run into it, so I generally only put a condition in the join (other than the actual join fields) when I am doing an outer join wher it may be needed to get the correct results. Did changing this one to the where clause solve your problem?

"NOTHING is more important in a database than integrity." ESquared

 

[kaht]

Did changing this one to the where clause solve your problem?

No, the results were the same

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[Chopstik]

kaht,

I know when I've occasionally run into issues where I was getting different data results that I've been able to trace it backwards to find the difference in the queries. In other words, take the extra results that are showing and see if you can determine why they would show in one but not the other.

But I agree with you in that I cannot see anything functionally different between the two queries.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[RiverGuy]

If you have multiple matching records in the REPHIERARCHY table in your join query, then you will get different results.

 

[SQLSister]

Well I can't see what it is but I agree with Chopstik, usually if you can see what the difference in the returned records is you can see why the problem.
this should get you the records that are being totaled in one but not the other (Adjust the on clause to the right fields)

select a.* from 
(select * 
from TRANSFERS..REPTRANSF t
   inner join TRANSFERS..REPHIERARCHY h
   on h.VP_ID = 'ES3891'
      and h.AGENT_UUID = t.AGENT_UUID
where t.COL_DAT = '2008-03-01')a
left join 
(select *
from TRANSFERS..REPTRANSF
where COL_DAT = '2008-03-01'
   and AGENT_UUID in (
      select AGENT_UUID 
      from TRANSFERS..REPHIERARCHY
      where VP_ID = 'ES3891'
   )) b
on a.field1 = b.field1--(use whatever would be the natural key in the record to join on or the primary key if you have one)
where b.agent_UUID is null

you might also switch it so the join goes the other way to see if the second query has records the first one doesn't have.


"NOTHING is more important in a database than integrity." ESquared

 

[kaht]

RiverGuy, you are absolutely correct. I was stupid to assume that there would be no duplicates in the data (you all know about Uncle Rico by now). It makes perfect sense how a duplicate AGENT_UUID in REPHIERARCHY would cause that agent's total to be counted twice in the REPTRANSF table using the join. When using the in + subquery method, we're essentially looking at the distinct AGENT_UUID's in the REPTRANSF table, and why it would only be counted once (correctly).

Thanks for pointing that out. A star for you.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]