CODEWITHSUNDEEP
t-sql
Stefan
Stefan

Reputation: 11509

SQL-query to filter on two fields in combination

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 so rows containing (company = 12 and storage = 1) and (company = 2 and storage = 1) will be filtered out of the result set and also filter on articlenr.

This is what I come up with, but sure there must be an easier way to make that query?

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 I'm after:

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

Upvotes: 7

Views: 11945

Answers (5)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This will return what you're 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 in this solution, which keeps it quite fast and performant. Besides you can easily keep adding restrinctions as pairs.

Upvotes: 2

trapper
trapper

Reputation: 11993

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'

Upvotes: 0

user554546
user554546

Reputation:

One possible way to make this easier is to set up a table called, say company_storage_exclude with columns of Company and Storage. Then simply populate these values with pairs of Company and Storage that you want to exclude, and you can then do:

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';

Upvotes: 0

Sam DeHaan
Sam DeHaan

Reputation: 10325

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

Upvotes: 3

Ben
Ben

Reputation: 1

Something along these lines?

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

Upvotes: 0

Related Questions