Query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query...

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-30 : 14:12:45

Hi Folks,
The following 2 queries I believe achieve the same thing but I get different results.
One is using a inner join syntax, the other is using a Correlated sub query format:

select count(*) from TBLAOM_W_DAY1SALES as A join TBL_D_ASSOCIATE B on A.ENRL_ASSOC_ID = B.COMMON_ASSOC_ID
where datediff(d,'01/01/2001',A.CST_ENRL_DT) Between B.Effective_date and isnull(B.Expired_date,2920490)
go

select count(*)
from TBLAOM_W_DAY1SALES as A
where ENRL_ASSOC_ID in
(select COMMON_ASSOC_ID from TBL_D_ASSOCIATE B
where A.ENRL_ASSOC_ID = B.COMMON_ASSOC_ID and datediff(d,'01/01/2001',A.CST_ENRL_DT) Between B.Effective_date and isnull(B.Expired_date,2920490) )
go

Why would the difference be caused?
Thanks

Ramdas Narayanan
SQL Server DBA

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-30 : 14:23:50

Well without see the DDL, in a 1 to many relationship you would get m,ore rows with the join than with the sub query...

Is that what you're seeing?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-30 : 14:26:55

Is COMMON_ASSOC_ID the primary key of TBL_D_ASSOCIATE ??

FYI -- always go with the first one, or some variation thereof, if possible. you are doing twice as much work in the second one it appears. (i could be wrong; I don't know anything about your tables structures or indexes)

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-30 : 14:27:11

..and I've never seen a correlated sub-query with an IN Clause before...

Didn't know that worked like that..

Why not:


SELECT count(*)
  FROM TBLAOM_W_DAY1SALES as A
 WHERE EXISTS 
	( SELECT 1 
	    FROM TBL_D_ASSOCIATE B 
	   WHERE A.ENRL_ASSOC_ID = B.COMMON_ASSOC_ID 
	     AND datediff(d,'01/01/2001',A.CST_ENRL_DT) 
		 BETWEEN B.Effective_date and isnull(B.Expired_date,2920490) 
	)
go

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-30 : 14:32:41

And how does this work?


AND datediff(d,'01/01/2001',A.CST_ENRL_DT) 
		 BETWEEN B.Effective_date and isnull(B.Expired_date,2920490)

Assuming that Effective and Expired are actual dates, how does that compare to an int returned from datediff?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-30 : 14:41:56

quote:
Originally posted by X002548

Well without see the DDL, in a 1 to many relationship you would get m,ore rows with the join than with the sub query...

Is that what you're seeing?
Yes, I get more rows with Join than the one with correlated Subquery.
Ramdas

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Ramdas Narayanan
SQL Server DBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-30 : 15:10:32

you probably missed my question, so I'll ask again:

is COMMON_ASSOC_ID the primary key of TBL_D_ASSOCIATE ??

- Jeff

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-30 : 16:17:04

Hi,
The column COMMON_ASSOC_ID has a non-clustered index on it. It is not a primary key. The Enrol_assoc_id on the TBLAOM_W_DAY1SALES table has a clustered Index on it.

Ramdas Narayanan
SQL Server DBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-30 : 16:32:47

if it is not the primary key, then that's why you are getting duplicates in your data and the count is different. Does this make sense?

If you join Table A -- > Table B, and you do not include ALL Primary key columns from Table B in the join, you will get duplicate rows potentially from Table A in the output.

- Jeff

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-30 : 16:48:53

Hi Folks,
Thank you
Ramdas

Ramdas Narayanan
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources