I don't know the answer (my SQL is very, very basic), but the way I read it is he is saying the PERMISSIONS column in the db is also sometimes a list.
Best advice I have is to normalize the db first. -----Original Message----- From: Doug R [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 04, 2007 1:46 PM To: CF-Talk Subject: Re: SQL select where in headache I think it is because of the single quotes around your comma delimited numbers. When I try to run a similar query, I get a data conversion error. If the column in the db is an INT, you do not need the single ticks around the var. That is only for varchar. Also, if it is varchar, each item would need to be surrounded by ticks (i.e. "... IN ('x','y','z') ) On 9/4/07, Hugh Fidgen <[EMAIL PROTECTED]> wrote: > > Hiya, > > I've got a problem selecting data from a table and I was wondering if > anyone could help. > > Code so far: > > <cfquery name="eventlist1"> > SELECT * FROM EVENTS > WHERE PERMISSIONS IN ('#session.status#') > ORDER BY EVENT_DATE, EVENT_START ASC > </cfquery> > > This works fine where field permissions = 1 but when permissions = 1,2,x,x > and so on the query fails. Basically - how can I loop through the comma > delimeted values in the permissions variable and compare them each against > #session.status# until a match is found. > > Thanks so much, > Hugh > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:287724 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
