That's a singularly horrible way to store a relation. First, the LIKE clause needs to have % at the front and back so that it doesn't search for exactly the string, but rather for the string with optional "stuff" at either end. Second, if the numbers aren't in the order you provide them, or they're separated by other numbers, the LIKE won't work properly. Third, you have to do some more nastiness to deal with matching "1,26,43,53,70", but NOT matching "1,726,43,539,7". Just doing a textual match won't work, because even though there isn't a "26" in the second list, the "26" part of "726" is textually equivalent.
Bottom line, never ever use lists to indicate relationships in a DB. That's the entire purpose of a relational database, and honestly, I'm not sure it's even possible to correctly handle list-based relationships with the DB. Even if it is possible, it's going to be insanely nasty, and very non-performant. cheers, barneyb On Tue, 16 Nov 2004 19:07:29 +0000, Stuart Kidd <[EMAIL PROTECTED]> wrote: > Hi guys, > > Thanks for the help on the List Contains stuff, once I get the rest of the > app done I will try and make the separate Relations table. > > I'm having problems with the LIKE function in SQL. > > In my relatedArticles field I have numbers like 26,43,53 > > <cfquery name="relatedArticles" datasource="user020"> > SELECT articleID, articleTitle > WHERE articleID LIKE '#GetArticle.articleRelatedLinks#' > </cfquery> > > <cfif relatedArticles.RecordCount GT 0> > Other articles that may relate to this story are:<br> > <cfoutput query="relatedArticles">#articleTitle#</cfoutput> > <cfelse>NOTHING here. > > </cfif> > > When I CFOUTPUT the relatedArticles it shows me 26,43,53 however when I use > the I try show the results in the <cfif> tag it actually thinks that no > articles have been pulled??? Strange, can anyone see what I am doing wrong? > > Thanks, > > Stu -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ I currently have 0 GMail invites for the taking ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184445 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
