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!
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!
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.
use cast\convert function to convert into varchar
more:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
more:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
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?
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 )
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 )