Link to home
Create AccountLog in
Avatar of gycitsupport
gycitsupport

asked on

How to use WHERE IN with a list of values from a table

Hi
I have a table which contains a list of values, for example :

id      cats
1       2000,2001,2002,2004,2005
2       20010,20011,20014,20015  

What I want is to be able to put the "cats" field into a WHERE IN statement within a stored procedure that I have written in SQL Server 2005.  Unsurprisingly really I get "Conversion failed when converting the varchar value to int" error because WHERE IN takes an int but I need to have a comma delimited list of values ("cats" in the example) to match against.

Any help would be gratefully received!
Avatar of chapmandew
chapmandew
Flag of United States of America image

you'll have to do it dynamically...post the code you're currently using, and I'll see if I can doctor it up for you.
use cast\convert function to convert into varchar
more:
 http://msdn.microsoft.com/en-us/library/ms187928.aspx
Avatar of gycitsupport
gycitsupport

ASKER

Hi
The code is a basic SELECT statement from one table but where cats in (2,3,4,5,100) or whichever set of values I need to use depending on the query. When I do a list like "..WHERE IN (2,3,4)" it works but when I try
SELECT * FROM tblX WHERE IN (SELECT cats FROM tblY WHERE ID=1) it doesn't.  

Is it possible to convert/cast a list of comma separated ints into one int value?
you need to put column name : where column in (select...)
sELECT * FROM tblX WHERE  .... IN (SELECT cats FROM tblY WHERE ID=1
                                                         /\
probably:
sELECT * FROM tblX WHERE  cats IN (SELECT cats FROM tblY WHERE ID=1
Hi
Sorry I meant to put the column name before the IN. Syntactically I have got it ok, it's just the conversion of the IN ( ) statement that I'm having an issue with.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks I think I may be able to sort something out with this function.
hmm:
if you get right result from

sELECT * FROM tblX WHERE  cats IN (1,2,3)
and
SELECT cats FROM tblY WHERE ID=1  
gives you
1
2
3
---the query below will give you desired result unless instead cats you need to use catID column:

sELECT * FROM tblX WHERE  cats IN (SELECT cats FROM tblY WHERE ID=1 )