Intersect - 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 2005 Forums

Transact-SQL (2005)

Intersect

Author

Topic

rn5a
Starting Member

25 Posts

Posted - 2008-11-04 : 03:54:08

What's wrong with the following query (it generates the Incorrect syntax near the keyword 'intersect' error)?

select storename from storeinfo
intersect
select storename from geography

Thanks,

Ron

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 04:02:11

its working fine for me. Is this whole query? or are you executing this as apart od another query?Also are you sure you're using sql 2005 with compatibility level 90?

rn5a
Starting Member

25 Posts

Posted - 2008-11-04 : 05:48:27

quote:
Originally posted by visakh16

its working fine for me. Is this whole query? or are you executing this as apart od another query?Also are you sure you're using sql 2005 with compatibility level 90?

Sorry I was trying it out in SQL Server 2000.

Thanks,

Ron

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-04 : 05:54:36

quote:
Originally posted by rn5a

quote:
Originally posted by visakh16

its working fine for me. Is this whole query? or are you executing this as apart od another query?Also are you sure you're using sql 2005 with compatibility level 90?
Sorry I was trying it out in SQL Server 2000.

Thanks,

Ron

It is not supported in SQL Server 2000

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 11:15:50

you can still simulate it like this

select storename
from
(
select storename,1 as cat from storeinfo
union all
select storename,2 from geography
)t
group storename
having count(distinct cat)=2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-05 : 00:54:19

or

select storename from storeinfo as t
where exists(select * from geography where storename=t.storename)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 02:10:09

quote:
Originally posted by madhivanan

or

select distinct storename from storeinfo as t
where exists(select * from geography where storename=t.storename)

Madhivanan

Failing to plan is Planning to fail

INTERSECT is always returning DISTINCT values.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-05 : 02:16:32

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

or

select distinct storename from storeinfo as t
where exists(select * from geography where storename=t.storename)

Madhivanan

Failing to plan is Planning to fail

INTERSECT is always returning DISTINCT values.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes. Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources