MS SQL two columns in the where clause at once checked for match

General Discussion
1 
select * from table
where (Line,Code) in (('A1',626),('A1',628),('B2',411))

I tried this, but it doesn't work as "in" only takes one parameter in MS SQL I think.

I want to grab all the rows that match some specific ordered pairs.

I figured it out with concat in a case.

2

Not necessarily a fan of this method, but it would probably work.

select * from table
where (Line + '|' + cast(Code as varchar(10))) in ('A1|626', 'A1|628', 'B2|411')
1 Like
3

Maybe you can't change it, but I'd question the database design that put order pairs in a single column. That doesn't even meet first normal form.

I failed to notice that the ordered pairs come from two different columns. Thanks for the correction.

1 Like
4

Use a constant valued table as an inner join:

Select alias1.*
From table inner join (
  Select 'A1' As Line, 626 as Code
  Union All
  Select 'A1', 628
  Union All
  Select 'B2', 411
) alias2 ON alias1.Line = alias2.Line AND alias1.Code = alias2.Code

{ Edit: removed redundant column names. }

It seems not to be the case in Zachary's example. Multi-column primary keys are common and very much compliant with first normal form on modern databases (that support multi-column constraints).

2 Likes
5

Sorry, I tried to post I had a solution already in the edit right after posting.

I used something like:
where 1= case( concat(Line,Code) in (concat('A1',626), concat(....),concat(..)) then 1 else 0 end
I think.