My mechanic likes to tell me that I should inform him what my car is/is not doing and let him do the diagnosing. Maybe I should try that with this problem also.
Using a form, 'JobFileUpload.cfm', files are uploaded to the server to a folder based on JobId and a then a record of jobId, file name, file type and access level/levels is inserted in the database table 'jobFiles'. I know I could do a another table with JobFileId's and Access levels, instead of a list of access levels, but it seems cleaner this way. On JobFileList.cfm, I want to filter on jobId and the current users access level which is stored in #session.PlumUserRoles# Any idea how I might do that? Thanks, Mark -----Original Message----- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 09, 2008 12:11 PM To: CF-Talk Subject: Re: SOT sql question 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:296279 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
