SQL IN Command and Comma Delminated Lists

[nix4]

I have a SQL Query in Cold Fusion:

<cfquery name=&quot;getspecials&quot; datasource=&quot;warehouse&quot; dbtype=&quot;ODBC&quot;>

SELECT * FROM specials
WHERE 88 IN (Itemlist) AND TYPE = &quot;PM&quot;

</cfquery>

Itemlist is a field of Specials that contains a comma seperated list ie 88,89,91,92

This Query is to find 88 in Itemlist and then check to see if the type field of speicials is also &quot;PM&quot;.

This dosnt work, any suggestions?

Basically Im looking for the ID of an item in the itemslist field using only SQL and not using a cold fusion LOOP.

Thanks,

Dave

 

[oj75]

Dave-- I'm not familiar with ColdFusion syntax but your SELECT statement may work if you turn it into a dynamic sql string and then execute you the string against sql server.
I would try storing the following in a string variable and execute it against the server using EXEC:

string specials = Itemlist;

string sql = &quot;SELECT * FROM specials
WHERE 88 IN (&quot; + specials + &quot&quot;

This should work but like I said I'm not sure about the ColdFusion syntax.


OJ
DB/.Net/CR Developer

 

[nix4]

I cannot pre-define a string for itemlist since thats exactly what Im searching for ...its a dynamic query. let me show you the undoctored version.

<cfquery name=&quot;getspecials&quot; datasource=&quot;warehouse&quot; dbtype=&quot;ODBC&quot;>
SELECT * FROM specials
WHERE '#itemloop.ID#' IN ('itemlist') AND TYPE = 'PM'
</cfquery>

Whats going on here is Cold Fusion is doing a Select Query from the table Specials and this query is in a loop for another table which is called itemloop.

So the first #itemloop.ID# is 88 then on the next iteration its 89. so the ID reference is changing so I cannot predefine itemlist.

 

[clarkin]

Try the ColdFusion forum, it's likely how you are forming your query rather than how SQL Server is handling it (SQL server just takes the plain text command delivered by CF - or ASP or whatever)

forum232 do it?

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[swampBoogie]

SELECT * FROM specials
    WHERE ',' + Itemlist + ',' like '%,88,%'
  AND TYPE = 'PM'

I'd recommend that you changed the datamodel though. Store each value in Itemlist as a separate value. With your current design your queries will be very slow as the DBMS must check each record and it can never make use of any indexes.