You're right...I've done this before and gotten assistance with it... Arrggh...it seems so long ago I can't remember exactly what the solution was.
The correct database design would involve a separate table containig the record ID and then the corresponding talents wouldn't it. Perhaps that is what I should do from the start rather than go this way. -Angel -----Original Message----- From: Bryan Love [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 2:20 PM To: CF-Talk Subject: RE: SQL : Finding element in list of values in a query field. First of all the DB design is incorrect for what you are trying to do... That being said, I've had to deal with this before and here is the best possible solution without changing the DB... 1) modify the GUI you use for data entry so that the talents list is inserted into the database with a comma at the beginning and at the end. So a list of talents looks like this in the DB: ",running,dancing,cooking," 2) run a query that will modify the data that is already in the DB by adding a comma before and after the talents string: UPDATE [table] SET talents = ',' + talents + ',' WHERE talents IS NOT NULL AND talents <> '' 3) now you can run queries like this: SELECT * FROM [table] WHERE talents LIKE '%,#aTalent#,%' +-----------------------------------------------+ Bryan Love Database Analyst Macromedia Certified Professional Internet Application Developer TeleCommunication Systems [EMAIL PROTECTED] +-----------------------------------------------+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis "Let's Roll" - Todd Beamer, Flight 93 -----Original Message----- From: Angel Stewart [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 10:34 AM To: CF-Talk Subject: SQL : Finding element in list of values in a query field. Hey all, Say for example I have a table: Firstname Lastname Address Talents. Talents for one record is: acting,dancing,singing. For another record it is : dancing,singing,music I want to construct a query to find all those with dancing as a talent. How would I do that using SQL...*can* I do that using SQL? -Angel ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
