Search code examples
sqlsql-like

NOT LIKE failing to exclude results SQL in query

I'm using a WHERE clause to exclude certain records from my query results.

where pv_table.docindex1 IS NULL AND
  pv_table.docindex2 IS NULL AND
  (data_file.vendornumber NOT IN (00550, 00510)
   OR data_file.invoicenumber NOT LIKE 'REB0000%')

This WHERE clause is successful in eliminating records with values 00550 and 00510 in the file.vendornumber column, but it is still including records that start with 'REB0000%' in the file.invoicenumber column in the query results.

Is there something wrong with my order of operations or my syntax using NOT LIKE ?

Solution

  • When using a WHERE NOT with multiple exclusion conditions, you need to use AND, not OR

    Example

    ColumnA
1
2
3
4
5

select ColumnA
from MyTable
where ColumnA = 1
or ColumnA = 5

    If I reverse this with where ColumnA <> 1 or ColumnA <> 5, then 1 <> 5 and 5 <> 1, so both appear in the results.

    So when we make a statement with a where not, we use AND to list the things we want to exclude