Using IN with an INT and varchar id list (eg: 1,4,5,6)

[calis2]

I need to be able to relate a varchar field that contains a list of id numbers (eg: 3,4,7,89,203) to a category id field (int). I've tried ::
C.category_id IN (SELECT mID = CAST ( M2.member_category as INT)
FROM member_details M2 WHERE M2.member_id = M.member_id)

It returns this rather understandable error ::
'Syntax error converting the nvarchar value '7,3,2' to a column of data type int.'

I would definitely like to find a way of doing this via one query rather than a looping subquery via CF. Any help would be greatly appreciated.
Mark

 

[Tim1]

Hiya Mark,

Just use the convert function.

C.category_id IN CONVERT(INT, (SELECT mID = CAST ( M2.member_category)
FROM member_details M2 WHERE M2.member_id = M.member_id))

I think that should work....

 

[tlbroadbent]

Create a dynamic SQL statement and execute it.

declare @sql varchar(1024)

Select @sql=
"Select .... " +
" Where C.category_id IN (" + M2.member_category + &quot"
FROM member_details M2 WHERE M2.member_id = M.member_id

Exec (@sql)

NOTE: @sql should look like

Select .... Where C.category_id IN (1,5,7,9,11)

You'll have to fill in the remainder of the select statement. Terry

http://members.home.net/tlbroadbent/index.htm

 

[calis2]

Thanks Terry & Tim.

Tim, I had actually tried that method and sql still appeared not to like it. (thx for your help netherless P)

However, that dynamic function is a great idea...just need to work out how to construct *dynamic sql* statements in CF.

thx