Post reply

not both

  • 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?

  • February 4, 2009 at 10:25 am

    #938172

    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

  • February 4, 2009 at 10:26 am

    #938173

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

  • February 4, 2009 at 10:26 am

    #938174

    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')

  • February 4, 2009 at 5:07 pm

    #938538

    OK, thanks.

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

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

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