CODEWITHSUNDEEP
sql-server
Danielle
Danielle

Reputation: 1

SQL Using OR and IN together

I am trying to select multiple OR clause with an IN clause. Below is my expression:

Select * from ABC
Where XYZ OR CDP OR Bell in (TermYard1,TermYard2,TermYard3,TermYard4)

I also tried

Select * from ABC
Where('XYZ','CDP','Bell') in (TermYard1,TermYard2,TermYard3,TermYard4)

I appreciate any help provided

Upvotes: 0

Views: 89

Answers (6)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

Another option using an in memory table

with q as (
  select 'XYZ' as Term
  union all select 'CDP'
  union all select 'BELL'  
)
select distinct ABC.* 
from   ABC
       inner join q on Term in (TermYard1, TermYard2, TermYard3, TermYard4)

cudos to Joel for further simplifying this
cudos to Paparazzi for fixing multiple rows

Upvotes: 2

paparazzo
paparazzo

Reputation: 45106

This has the problem of multiple term matches will duplicate rows
select distinct would fix that 

select abc.* 
  from ( values ('XYZ'), ('CDP'), ('Bell')
       ) term(t)    
   join abc 
     on term.t in (TermYard1, TermYard2, TermYard3, TermYard4)

Upvotes: 1

EzLo
EzLo

Reputation: 14209

To do this you have 2 options:

Repeat the IN list item for each column you want to filter:

Select 
    * 
from 
    ABC
Where
    XYZ in (TermYard1,TermYard2,TermYard3,TermYard4) OR
    CDP in (TermYard1,TermYard2,TermYard3,TermYard4) OR
    Bell in (TermYard1,TermYard2,TermYard3,TermYard4)

Create a table with the IN values and do an EXISTS with multiple OR.

IF OBJECT_ID('tempdb..#YardTerms') IS NOT NULL
    DROP TABLE #YardTerms

CREATE TABLE #YardTerms (Term VARCHAR(100))

INSERT INTO #YardTerms (Term)
VALUES
    ('TermYard1'),
    ('TermYard2'),
    ('TermYard3')

Select 
    * 
from 
    ABC
WHERE 
    EXISTS (SELECT 1 FROM #YardTerms WHERE XYZ = Term OR CDP = Term OR Bell = Term)

Upvotes: 0

KarloX
KarloX

Reputation: 987

You can't do that.

Assuming XYZ and CDP and Bell are fields of table (or view) ABC, you can write:

Select * from ABC
Where XYZ in (TermYard1,TermYard2,TermYard3,TermYard4) OR 
      CDP in (TermYard1,TermYard2,TermYard3,TermYard4) OR 
      Bell in (TermYard1,TermYard2,TermYard3,TermYard4);

Upvotes: 0

Jorge Y.
Jorge Y.

Reputation: 1133

If you don't mind putting the same list 3 times...

Select * from ABC
Where XYZ  in (TermYard1,TermYard2,TermYard3,TermYard4)
 OR CDP  in (TermYard1,TermYard2,TermYard3,TermYard4)
 OR Bell in (TermYard1,TermYard2,TermYard3,TermYard4)

Upvotes: 0

MatSnow
MatSnow

Reputation: 7537

Try this:

SELECT * FROM ABC
WHERE
XYZ IN (TermYard1,TermYard2,TermYard3,TermYard4) OR
CDP IN (TermYard1,TermYard2,TermYard3,TermYard4) OR
Bell IN (TermYard1,TermYard2,TermYard3,TermYard4)

Upvotes: 2

Related Questions