SQL query to filter by two fields in combination

Question

ArticleNumber Company Storage 01-01227 12 2 01-01227 2 1 'filtered by company/storage in combination 01-01227 5 1 01-01227 12 1 'filtered by company/storage in combination 01-44444 5 4 'filtered by not match the articlenumber 

I want to filter lines containing ( company = 12 and storage = 1 ) and ( company = 2 and storage = 1 ) will be filtered from the result set, and also filtered on articlenr .

Is this what I came up with, but sure there should be an easier way to make this request?

 SELECT * FROM MyTable where (Company=2 and Storage<>1 and ArticleNumber='01-01227') or (Company=12 and Storage<>1 and ArticleNumber='01-01227') or (Company<>2 and Company<>12 and ArticleNumber='01-01227') 

The result that I get after:

 ArticleNumber Company Storage 01-01227 12 2 01-01227 5 1 

Answer 1

This will return what you are looking for:

 select * from t where articleNumber = '01-01227' and ( (company != 12 or storage != 1) and (company != 2 or storage != 1) ) 

Result:

 ARTICLENUMBER COMPANY STORAGE 01-01227 12 2 01-01227 5 1 

There is no need to join this solution, which saves it fairly quickly and efficiently. In addition, you can easily add constraints in the form of pairs.

Answer 2

 SELECT * FROM MyTable WHERE ArticleNumber='01-01227' AND (Company NOT IN (2,12) OR Storage <> 1) 

Answer 3

One possible way to make this simpler is to create a table called company_storage_exclude with Company and Storage columns. Then simply fill these values ​​with the Company and Storage pairs that you want to exclude, and you can do the following:

 select * from MyTable where (Company,Storage) not in (select Company,Storage from company_storage_exclude) and ArticleNumber='01-01227'; 

or

 select a.* from MyTable a left join company_storage_exclude b on (a.Company=b.Company and a.Storage=b.Storage) where b.Company is null and b.Storage is null and ArticleNumber='01-01227'; 

Answer 4

 SELECT * FROM MyTable WHERE NOT (Company=12 and Storage=1) AND NOT (Company=5 and Storage=1) AND ArticleNumber='01-01227' 

or even better

 SELECT * FROM MyTable WHERE NOT ((Company=12 OR Company=5) AND Storage=1) AND ArticleNumber='01-01227' 

Answer 5

Something like that?

 SELECT * FROM MyTable where ArticleNumber='01-01227' AND (Company IN (2,12) AND Storage <> 1 OR Company NOT IN (2,12) )