CODEWITHSUNDEEP
sqlselect
WilliamKF
WilliamKF

Reputation: 43219

How to form an SQL query that is like using 'IN' across two columns as pairs?

I have two integer columns and I wish to select rows with particular pairings of values. What SQL syntax can I use? For example, using IN it might look something like this if IN supported this syntax:

select * 
 from myTable 
 where value1, value2 in ((2,3), (3,4), (2,5), (3,6))

To select those rows with value1 == 2 and value2 == 3 or value1==3 and value2==4 or 2/5 or 3/6.

I'm using a proprietary SQL system, so basic SQL is preferred. Or if there is none, having a statement that works in some standard SQL would be useful as well.

Upvotes: 2

Views: 115

Answers (3)

solaimuruganv
solaimuruganv

Reputation: 29857

same can be achieved by using VALUES 

select table_name.* 
  from table_name tn, 
       (values(2,3), (3,4), (2,5), (3,6) ) as val(v1,v2) 
  where tn.value1 = val.v1 and tn.value2 = val.v2

Upvotes: 1

podiluska
podiluska

Reputation: 51514

select yourtable.* 
from yourtable
     inner join 
     (
          select 2 as v1, 3 as v2 
          union select 3,4
          union select 2,5
          union select 3,6
     ) pairs
         on yourtable.value1 = pairs.v1
         and yourtable.value2 = pairs.v2

Upvotes: 5

Abe Miessler
Abe Miessler

Reputation: 85126

Well in SQL Server you can't use IN that way unfortunately. I think your best bet is going to be to write it out like you did below your code sample or to load your data into a CTE or something and then joining on that.

Upvotes: 1

Related Questions