SQL WHERE excluding records using AND and NOT

Question

Here's a query I'm working on:

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND ( poe.[TBL_COMPANY] <> '011'
              AND poe.[TBL_DIVISION_1] <> '11'
            )

What I want returned are all the records that are not in Company = '011'/Division_1' = '11'.

I thought combining the company/division in the () would achieve this, but it does not. It eliminates all the company '011' records and it eliminates all division '11' records.

However, when I do this:

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND NOT ( poe.[TBL_COMPANY] = '011'
              AND poe.[TBL_DIVISION_1] = '11'
            )

it seems to pull the correct results. Why is this?

Answer 1

where x and (y and z)

is the same as

x and y and z

however

where x and not (y and z)

is what you want, and works.

try this:

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND NOT ( poe.[TBL_COMPANY] = '011'
                  AND poe.[TBL_DIVISION_1] = '11'
                )

Answer 2

I think the equivalent to the NOT would use OR as shown below:

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND ( poe.[TBL_COMPANY] <> '011'
              OR poe.[TBL_DIVISION_1] <> '11'
            )

Answer 3

It's a boolean logic issue:

NOT (A and B) <=> NOT A  OR NOT B

i.e.:

NOT ( poe.[TBL_COMPANY] = '011' AND poe.[TBL_DIVISION_1] = '11')

Is equivalent to:

( poe.[TBL_COMPANY] <> '011' OR poe.[TBL_DIVISION_1] <> '11')

Read:

http://en.wikipedia.org/wiki/Boolean_logic

Answer 4

Because records not belonging to both Company 011 and Division 11 are matched by

          poe.[TBL_COMPANY] <> '011'
          OR poe.[TBL_DIVISION_1] <> '11'

If company is not 011, division can be 11 and you'll still have a matching combination.

De Morgan's law explains how to convert boolean expressions in such a manner.

Answer 5

Time for some boolean algebra. This:

not (x and y)

is the same as:

(not x or not y)

So your query should be:

SELECT  TBL_SUB_KEY AS port
      , poe.[TBL_COMPANY]
      , poe.[TBL_DIVISION_1]
FROM    dbo.TMVKTAB AS poe
WHERE   ( TBL_NUMBER = '8A' )
        AND ( TBL_SUB_KEY <> '' )
        AND ( poe.[TBL_COMPANY] <> '011'
              OR poe.[TBL_DIVISION_1] <> '11'
            )