Select records where except A or B

Question

I am having trouble converting the following human language to MySQL query:

Select all rows except those with ColA equals '1' OR ColB equals '1'.

I tried:

SELECT * FROM table WHERE ColA = '1' OR ColB = '1';

The above will pull records that matches the where clause above (either ColA = '1' or ColB = '1'), but how do I get the results opposite of that? I hope I am describing it correctly. Please let me know if it is confusing.

Answer 1

select
    *
from
    `myTable`
where
    `colA` <> 1 and
    `ColB` <> 1

This will work for you only if the colA and colB are NOT NULL. If there are rows with colA = NULL or colB = NULL you have to change the condition in your request.

select
    *
from
    `myTable`
where
    (`colA` <> 1 or `colA` is null) and 
    (`colB` <> 1 or `colB` is null)

Of course it depends on if you want the rows with NULL values, but many people forget about this.

Here is SQL Fiddle example http://www.sqlfiddle.com/#!2/ea797/2