not both

Question

Post reply

not both

Donatello

SSC Eights!

Points: 853
More actions
February 4, 2009 at 10:21 am

#155228

Good Day,
I am working on a query for SQL Server 2005 and need some
help with a logic problem. I want to add to the WHERE clause
some logic to exclude a record when two conditions obtain,
but not when they obtain individually.
Here is the query:
SELECT Server
,Drive
,StorageUsed
,StorageFree
,PercentFree
,DateChecked = getdate()
FROM dbo.DiskStorage
WHERE PercentFree < 21
I want to exclude a specific drive on a specific host by
adding to the WHERE clause something that says "not
both Server='SQL005' and Drive = 'G'". I want to continue
to see the output all the other drives on the host just leave
the one drive out of the output.
How can I add a "not both" condition to the WHERE clause?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi

Put it in brackets, like this:

AND NOT (Server = 'SQL005' and Drive = 'G')

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Michael Valentine Jones

SSC Guru

Points: 64818

February 4, 2009 at 10:26 am

#938173

where not (Server='SQL005' and Drive = 'G' )

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Gaius Baltar (2/4/2009)
Good Day,
I am working on a query for SQL Server 2005 and need some
help with a logic problem. I want to add to the WHERE clause
some logic to exclude a record when two conditions obtain,
but not when they obtain individually.
Here is the query:
SELECT Server
,Drive
,StorageUsed
,StorageFree
,PercentFree
,DateChecked = getdate()
FROM dbo.DiskStorage
WHERE PercentFree < 21
I want to exclude a specific drive on a specific host by
adding to the WHERE clause something that says "not
both Server='SQL005' and Drive = 'G'". I want to continue
to see the output all the other drives on the host just leave
the one drive out of the output.
How can I add a "not both" condition to the WHERE clause?

Without going into an explanation try this:

SELECT

Server

,Drive

,StorageUsed

,StorageFree

,PercentFree

,DateChecked = getdate()

FROM

dbo.DiskStorage

WHERE

PercentFree <= 21 AND

NOT(Server = 'SQL005' and Drive = 'G')

Donatello SSC Eights! Points: 853 More actions · Answer 4

OK, thanks.

I was trying different variations on this and did not realize you can negate multiple arguments in that way.