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!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
EugeneZ

8/22/2022 - Mon
chapmandew

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.
EugeneZ

use cast\convert function to convert into varchar
more:
 http://msdn.microsoft.com/en-us/library/ms187928.aspx
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
EugeneZ

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
gycitsupport

ASKER
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
chapmandew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
gycitsupport

ASKER
Thanks I think I may be able to sort something out with this function.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
EugeneZ

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 )