How to use AND condition in IN clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to use AND condition in IN clause

Author

Topic

bhushan.andhare
Starting Member

3 Posts

Posted - 2015-04-13 : 06:47:24

I have a simple query where I am using IN clause to search for numbers on column col1 .

The col1 column stores comma seperated numbers like '12,34,50'.

The query is:

Select * from TableName where col1 IN (12,34)

In the above query I am looking for numbers 12, 34 and 50. It works fine. Above query looks for 12 or 34 in the col1 . But I would like to search for both 12 and 34. Instead of using the OR condition I would like to use AND condition.

Any ideas?

Bhushan Andhare

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-13 : 08:54:47

the IN operator doesn't work the way you think it does. In actuality, it is just shorthand for this:


where col1 = 12 or col1 = 24

clearly a value of '12,34,50' will not satisfy either test. You probably want something like this:


where (col1 = 12 OR col1 like '12,%' or col1 like '%,12,% or col1 like '%,12')
  and (col1 = 34 OR col1 like '34,%' or col1 like '%,34,% or col1 like '%,34')

bhushan.andhare
Starting Member

3 Posts

Posted - 2015-04-14 : 06:47:49

Hi gbritton,

Thanks for your reply.

I will explain it clearly.

I am passing the comma separated value as a parameter in a stored procedure. for example '12,34'

then in a stored procedure i would like to check if (col1 = 12 and col1=34 )

But as I am passing the value i would not know how many values I have passed.

I hope it helps you to understand my query.

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 07:15:01

You need a function that will SPLIT your comma delimited list and then compare it against the col1 delimited list


CREATE PROCEDURE MySProc
   @MyValueList varchar(8000)     -- e.g. '12,34'
AS
  SELECT Col1, Col2
  FROM MyTable
       JOIN dbo.MySplitFunction(@MyValueList)
           ON ',' + col1 + ',' LIKE '%,' + MySplitValue + ',%'

but it is a TERRIBLE way to do this job. You should have a CHILD TABLE with each individual value in the Col1 list and then you could just do


SELECT COl1, Col2
FROM MyParentTable AS P
     JOIN MyChildTable AS C
       ON C.ID = P.ID
WHERE C.Col1 IN (12, 34)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-14 : 10:45:41

" i would like to check if (col1 = 12 and col1=34 ) "

Since that is clearly impossible, do you actually mean:

i would like to check if (col1 contains both 12 and 34 )

?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-14 : 12:13:35

It is called Relational Division.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

bhushan.andhare
Starting Member

3 Posts

Posted - 2015-04-15 : 10:34:58

Hiii gbritton,

Yes I would like to check if (col1 contains both 12 and 34 )

where the col1 value is comma seperated numbers.

So i would like to check contains.

But I do not want to use IN clause as it checks for OR condition.

Thanks.

Bhushan Andhare

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 12:01:12

Use what I posted last week:


where (col1 = 12 OR col1 like '12,%' or col1 like '%,12,% or col1 like '%,12')
  and (col1 = 34 OR col1 like '34,%' or col1 like '%,34,% or col1 like '%,34')

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-16 : 12:03:06

That may not work correctly.

You need to do this:

where ',' + col1 +',' like '%,12,%'
and ',' + col1 +',' like '%,34,%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources