Return All records except - 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)

Return All records except

Author

Topic

rweasel6
Starting Member

6 Posts

Posted - 2009-09-28 : 14:20:44

What is the right way to write a query to return all records except for those that meet a couple of criteria.

I want to have all records come back except for those that have a project code of 1 and year of 2009 and Qtr>1. All 3 criteria would have to be met together to be excluded.

I was able to write a query with a NOT IN statment based on a subquery that searches for those records but this requires an key. Im wondering how to write this if you don't have a unique key.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-28 : 14:44:50

select <records>
from yourTable
where [year] = 2009 and code = 1 and qtr > 1

Jim

Everyday I learn something that somebody else already knew

rweasel6
Starting Member

6 Posts

Posted - 2009-09-28 : 15:07:19

quote:
Originally posted by jimf

select <records>
from yourTable
where [year] = 2009 and code = 1 and qtr > 1

Jim

Everyday I learn something that somebody else already knew

Thanks for the response.

What Im trying to do is show all records EXCEPT for the ones that I have the criteria for. I need the criteria evaluated together though.

So when I say that Im looking at year=2009 and Qtr>1 I still expect the query to return records for years other than 2009 just not ones where it equals 2009 and Qtr>1 and ProjCode=1.

Does this make sense?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-28 : 20:31:24

Yeah, that makes sense. Sorry I didn't get back to you sooner, but sometimes my boss makes me do my job first!

Is there an identifier(id) to each record? If so, you can do something like this:

Select <yourRecords>
from yourTable
where id not in (select id
from your table
where [year] = 2009 and projcode <> 1 and qtr > 1)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources