2 select in if exists - 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)

2 select in if exists

Author

Topic

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-04-06 : 14:51:24

I have an If Exists problem. Here is my query

if exists (select r.regionid from region r
inner join franchiseregionvenue frv on r.regionid = frv.regionid
inner join siteregionvenue srv on frv.franchiseregionvenueid = srv.franchiseregionvenueid
inner join business b on srv.businessid = b.businessid and b.deletedate is null
inner join address a on b.businessid = a.businessid and a.deletedate is null
where r.regionid != @regionid and
postalcode like convert(varchar(10), @postalcodename) + '%')
!=
(select r.regionid from region r
inner join regionpostalcoderef rp on r.regionid = rp.regionid
where postalcode = @postalcodename

It keeps giving me an error message incorrect syntax near '!' What would be the best way to do this??

Any help?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-06 : 14:59:00

IF EXISTS will only return a TRUE of FALSE irrespective of what you have in your SELECT statement. You can get the RegionID from both the SELECTs into separate variables and compare.

************************
Life is short. Enjoy it.
************************

cjhardie
Yak Posting Veteran

58 Posts

Posted - 2007-04-06 : 15:14:41

The problem is the first select statement can return multiple results, How do I compare that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-06 : 15:35:56

It doesn't matter. When you use EXISTS the SQL engine quits searching after found the first item.
That's why EXISTS often is more efficient than IN.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 03:06:42

I doubt you need this


if exists 
(
    select r.regionid from region r
    inner join franchiseregionvenue frv on r.regionid = frv.regionid
    inner join siteregionvenue srv on frv.franchiseregionvenueid = srv.franchiseregionvenueid
    inner join business b on srv.businessid = b.businessid and b.deletedate is null
    inner join address a on b.businessid = a.businessid and a.deletedate is null
    where r.regionid != @regionid and
    postalcode like convert(varchar(10), @postalcodename) + '%'
    !=
    (select r.regionid from region r 
    inner join regionpostalcoderef rp on r.regionid = rp.regionid
    where postalcode = @postalcodename
    )
)
--do If stuff
else
--do else stuff

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-04-07 : 07:01:01

Are you looking for somthing like this


if exists 
(
			select 1 from region r
		inner join franchiseregionvenue frv on r.regionid = frv.regionid
		inner join siteregionvenue srv on frv.franchiseregionvenueid = srv.franchiseregionvenueid
		inner join business b on srv.businessid = b.businessid and b.deletedate is null
		inner join address a on b.businessid = a.businessid and a.deletedate is null
		where r.regionid != @regionid and
		postalcode like convert(varchar(10), @postalcodename) + '%')
		Not Exists 
		(
			select 1 from region r1
			inner join regionpostalcoderef rp on r1.regionid = rp.regionid
			where postalcode = @postalcodename
			And r.regionid = r1.regionid 
		)
)
--do some processing 
Else 
--do some processing

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-10 : 05:15:05

That looks good

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources