select word from dictionary where (select dream from dreams where dream_id = #dreamid#) like '%#word#%')
can be done like this, without the () around the %#word#% part.... ..tony Tony Weeg Senior Web Developer UnCertified Advanced ColdFusion Developer Information System Design Navtrak, Inc. Mobile workforce monitoring, mapping & reporting www.navtrak.net 410.548.2337 -----Original Message----- From: Ben Doom [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 12:06 PM To: CF-Talk Subject: RE: Is there a Better Way to do this? I'm not a SQL wizard and am doing this off the top of my head, but I'd try something along the lines of select word from dictionary where (select dream from dreams where dream_id = #dreamid#) like ('%' + word + '%') But, again, that's off the top of my head. --Ben Doom Programmer & General Lackey Moonbow Software : -----Original Message----- : From: Kelly Matthews [mailto:[EMAIL PROTECTED]] : Sent: Thursday, December 19, 2002 11:38 AM : To: CF-Talk : Subject: Is there a Better Way to do this? : : : Ok here is what i am doing and I am just trying to find out if 1. : there is a : better way and 2. if it can be done on the SQL side (as a stored proc) : instaed of the CF side. Just not all that familiar with looping : outside of : CF. : : ANyway I have one table that is a dictionary with about 1500 : words. THen I : have another table that has dreams people have entered. THe idea : is to take : the list of words and see if any of them show up in the dream and then : display those words to the user. What I built works and it's : actually pretty : speedy, but the dictionary is very small right now and it may be : closer to : 10000 by the time we are done which is a MUCH larger list of : words to loop : through. : : So right now I am doing a query to get the words : <CFQUERY name="wordS"> : SELECT word : from dictionary : </CFQUERY> : : THen we grab a dream : <CFQUERY name="dream"> : SELECT dream : from dreams : where dream_id = 3 : </CFQUERY> : : Then I Loop through like so: : <CFLOOP list="#valuelist(words.word)#" delimiters="," index="theword"> : Then I check to see if the dream contains the word. : <CFIF dream.dream contains " #theword# " OR dream.dream contains " : #theword#s> : Then I grab the definition if it found that word. : <CFQUERY name="getdef"> : SELECT definition from dictionary : where word = '#theword#' : </CFQUERY> : Then I display it #theword#<BR>#getdef.definition#<P> : </CFIF> : </CFLOOP> : : This words fine and with a dream thats a few thousand words it : completes in : a few seconds. I just wonder if there is a faster or better way : to do this : so down the road we don't run into problems as the word list grows. : THoughts? : : : : : ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
