Post reply

Cannot make "AND' and "<>" work together

  • July 17, 2009 at 4:53 pm

    #134442

    This seems so simple I can't believe I can't get it to work.

    SELECT Field1, Field2 FROM mytable WHERE id = 2 OR id = 3

    Returns 2 records; however if Field1 contains 'X' I don't want it. Field1 is 'X' in ID = 2 and Field1 is NULL in ID = 3.

    SELECT Field1, Field2 FROM mytable WHERE (id = 2 OR id = 3) AND (Field1 <> 'X')

    Returns 0 records!

    SELECT Field1, Field2 FROM mytable WHERE id IN (2,3) AND (Field1 <> 'X')

    Also returns 0 records. What am I doing wrong?

    :blush: Found it! (Can't seem to delete this post)

    SELECT Field1, Field2 FROM mytable WHERE (id = 2 OR id = 3) AND (Field1 <> 'X' OR Field IS NULL)

  • July 18, 2009 at 10:26 am

    #1026576

    We don't allow deleting of posts, and we're prefer if you figure it out, just post again.

    Thanks for the update.

  • July 18, 2009 at 10:46 am

    #1026578

    Kenneth Gladden (7/17/2009)

    What am I doing wrong?

    The only comparisons with null that can return true are IS NULL and IS NOT NULL

    http://sqlinthewild.co.za/index.php/2008/01/16/comparisons-with-null/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2009 at 11:19 am

    #1026582

    Steve Jones - Editor (7/18/2009)

    We don't allow deleting of posts, and we're prefer if you figure it out, just post again.

    And you can't remove the delete button?

  • July 18, 2009 at 11:45 am

    #1026587

    Probably could, and should.

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

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