SELECT with not 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 2000 Forums

SQL Server Development (2000)

SELECT with not exists

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-12-27 : 17:14:20

I have this Select working fine.

select distinct top 1000 accountnumber, sum(JobTotal) from dbo.stsJobHistory where DateEntered>'2005-12-31'
group by accountnumber order by sum(JobTotal) desc

But when I added another where like below, it dose not like it.

select distinct top 1000 accountnumber, sum(JobTotal) from dbo.stsJobHistory where DateEntered>'2005-12-31'
and accountnumber not exists in (Select account_number from #TopAccounts)
group by accountnumber order by sum(JobTotal) desc

What is the alternative? Thanks!!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 17:24:39

Your syntax is a little off, try this


select distinct top 1000 accountnumber, sum(JobTotal) from dbo.stsJobHistory where DateEntered>'2005-12-31' 
and not exists (Select * from #TopAccounts where account_number = accountnumber)
group by accountnumber order by sum(JobTotal) desc

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-12-27 : 17:49:10

Thank you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 03:01:42

1 When you use Exists you cant use IN
2 Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources