On Jan 9, 2008 8:56 AM, Mark Fuqua <[EMAIL PROTECTED]> wrote: > I think I need to be using 'IN' within my WHERE clause but I can't seem to > get it to work. I have a column with a comma delimited list. This is the > latest attempt and it craps out too. > > SELECT.. > ...WHERE JobFileJob = #session.jobId# AND '#session.UserRole#' IN > (JobFileAccessLevel) > > JobFileAccessLevel is a field in the database table that contains a comma > delimited list of access levels.
you're doing it backwards :) SELECT foo FROM bar WHERE foobar IN (a,b,c,d) says "give me records where the value of foobar is either a OR b OR c Or d. "IN", is a shorthand way of writing out a number of OR statements. it'd be wrong of me to not state here that generally speaking, storing comma delimited values in a relational database table is (again, generally) not a good idea and defeats the purpose of a relational database. those values should (likely) be broken out into a new table as individual rows (with an ID column having a FK relationship back to the original table). -- "Scientists tell us that the fastest animal on earth, with a top speed of 120 feet per second, is a cow that has been dropped out of a helicopter." - Dave Barry ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296268 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
