Off of the top of my head you can do a several things.
1. stored processdure and loop through the list using tsql while loop
Declare @TheList nvarchar(250) ,@RowNum int,@Item char(5)
-- Need to tack a delimiter onto the end of the input string if one
doesn't exist
SET @TheList= (SELECt TestList +',' FROm dbo.tblTestLoop)
declare @pos int
set @pos = charindex(',' , @TheList)
while @pos <> 0
begin
set @Item = left(@TheList, @pos - 1)
------- Table I want results from-----------------
select *
FROM dbo.tblFAQ
WHERE FAQID =(cast(@Item AS int))
set @TheList= stuff(@TheList, 1, @pos, '')
set @pos = charindex(',' , @TheList)
end
2. use Dynamic sql
DECLARE @Sql nvarchar(2000)
SET @Sql = 'select * FROM dbo.tblFAQ WHERE FAQID IN('+
(SELECt TestList FROm dbo.tblTestLoop) +')'
EXEC(@Sql)
3. create freetext index and use the contains key word.
If you have the comma delimited list of values stored in a single field in
the database, how can you do a select statement to get all of the records
that have a certain value as one of the values in the comma delimited list?
In this case, I'm trying to assign multiple classifications (classifications
are stored in lookup table) to one particular image record (foreign keys
from lookup table are stored in the comma delimited list.) Am I approaching
this in the wrong way?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2831
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
