Hello Good Evening,

Can you please advise with this is there any possibility or smart way to do this?

from this Query

select * from MyTable
where Column1 in ('12345' ,'23456') OR Column2 in ('12345' ,'23456') OR
Column3 in ('12345' ,'23456')

TO

select * from MyTable where ('12345' ,'23456') in (Column1, Column2,Column3 )

an year back i heard we can do this, i did somehow but not sure, Could you please help me if there is any possibility

Thank you much in Advance

Milan

Just fyi,

this is working fine with one value

select

*from MyTable

where

'12345

ThanksIn(Column1,Column2,Column3 )

Just fyi,

this is working fine with one value

select

*from MyTable

where

'12345

ThanksIn(Column1,Column2,Column3 )

I never used this method...i just tired this now..

somehow, i do not think it is possible to have two values on the right hand side... may be you should use OR condition

declare @table table(sno int,sno1 int,sno2 int)

insert into @table values(1,1,1),(2,1,2),(3,6,2)

select * from @table where ('1') in (sno1,sno2,sno) or ('3') in (sno1,sno2,sno)  

Stan Thank you much for your reply,

It works as you suggested but just looking for more comments.,

Thank you Again Stan

They lied to you :( 

Good day Milano85

Using the format with "where (<more than one value>) in (...)" is incorrect. one option is to use Stan210 solution which mean that you need to write each condition by yourself. It will work but it might take time and it might not fit for dynamic situations were you don't have the list of conditions.

there is a simple and clean solution that you can use. we can use CROSS JOIN (which give us all the options of member in one SET equal to a member in the other SET).

for example:

declare @table table(sno int,sno1 int,sno2 int)
insert into @table values(1,1,1),(2,1,2),(3,6,2)

select distinct sno,sno1,sno2, C
from @table T1
CROSS JOIN (values (1),(3)) as T2(C) -- here you can use any value that you want to check
where C in (sno,sno1,sno2)
GO

* In most cases using JOIN might use more resources, since we do another operation of dynamically get the first SET and JOIN it, but this will be much simpler and faster to write for long list (first SET) and it can be dynamically use. There some other solutions, but In any case you should check a query according to your DDL+DML and without DDL this is only a theoretical discussion.

* you can use this solution in SP or any other clock of code using Table Value Parameter. You can create new type (table type) and get the values as one parameter input in SP.

I agree with  pituach answer but don't select "C" column.

• Edited by Vitaliy Lukashev 7 minutes ago