For futures use had you normalized the table, and created a one to many relationship between the news table, and another table lets call it newsViews ( I really don't know your structure to come up with something better) you won't have this problem, then you could run a join, and use the IN operator with your list and not have a problem. Again this is letting the DB do its job. I have however had some funky requests which pretty much required me not to do it right, and you probably already know this huh?
Tim Heald ACP/CCFD :) Application Development www.schoollink.net > -----Original Message----- > From: Timothy Heald [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 09, 2002 10:50 PM > To: CF-Talk > Subject: RE: Looping in a Query > > > You can't do that. You will have to do the comparison after the query. > This should sort you: > > <cfquery name="getnews" datasource="#request.MainDSN#" debug> > SELECT n.createdby, n.id, n.category, n.createdon, n.killdate, n.body, > n.viewby > FROM news n > WHERE n.category = 3 AND > n.killdate >= #attributes.currenttime# > </cfquery> > > That will get the data. Now you need to compare your list to the list you > just pulled from the db, so something like: > > <CFSET viewable = 0> > <CFLOOP list="#getNews.viewBy#" index="listItem"> > <CFIF listContains(session.curruser.offices.list, > listItem) or getNews.ID > = 1> > <CFSET viewable = 1> > </CFIF> > </CFLOOP> > > Then you can test for the variable viewable to see weather or not > to display > the news article. > > Should do the trick. > > Tim Heald > ACP/CCFD :) > Application Development > www.schoollink.net > > > -----Original Message----- > > From: John Kivus [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, May 09, 2002 10:37 PM > > To: CF-Talk > > Subject: Looping in a Query > > > > > > I'm having a mental block trying to write a query. I have a list of > > numbers (which are in no particular order) and I need to see if just one > > of those numbers is in a field in the database which also has a list of > > numbers. I figured I'd use a <cfloop> inside my query by I'm having a > > little trouble. When I try > > > > <cfquery name="getnews" datasource="#request.MainDSN#" debug> > > SELECT n.createdby, n.id, n.category, n.createdon, n.killdate, n.body, > > n.viewby > > FROM news n > > WHERE n.category = 3 AND > > n.killdate >= #attributes.currenttime# AND ( > > <cfloop list="#session.curruser.offices.list#" index="e"> > > #ListContains(n.viewby, e)# <> 0 OR > > </cfloop> > > n.id = 1 ) > > </cfquery> > > > > > > it cannot resolve "n.viewby". Am I really missing the boat on this one? > > Or I do I have a minor mistake? For some reason I think there just must > > be a better way. > > > > Sincerely, > > John Kivus > > > > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > > Archives: http://www.mail-archive.com/[email protected]/ > > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
