I think this is where you need normalisation of your 'events_placement' data. Storing the event placements as a list of ids makes it a nightmare to do this sort of query. Instead, have a separate table called 'events_placement' with the following fields:
Events_Placement_ID (PK - autoincrementing integer) Events_ID (FK) Placement_ID (FK) - or a better name, related to what those ids are linked to So now, for each placement in an event, you have a single row of data in the 'events_placement' table which makes it a breeze to query. Now your query can look like this: SELECT Events_ID, Events_Placement FROM events_main WHERE 6 IN (SELECT Placement_ID FROM Events_Placement WHERE Events_Placement .Events_ID = events_main.Events_ID ) HTH Dominic 2008/5/27 Paul Ihrig <[EMAIL PROTECTED]>: > Hey Guys! > i am currently doing a LIKE to see if the value is in a list > but i know this is wrong because as the list grows i will run into problems > say my url var is cfm?n1Id=6 > > i can do on my select statement > AND Events_Placement LIKE (N'%#URL.n1Id#%') > but am afraid if i get a 16 or 61 i will hose every thing... > > > SELECT Events_ID, Events_Placement > FROM events_main > WHERE (NOT (Events_Placement IS NULL)) > > returns > > Events_ID Events_Placement > 94 0,5,6 > 124 0,2,4 > > so i want to do > AND 6 IN (Events_Placement) > > but i keep getting error > converting varchar to data type init > > i have tried casting, with no luck > also CONTAINS... > > any help would be great! > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306061 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
