I think you're right about the valueList approach- one more question though- is it possible to run an ORDER BY statement through a list so something like:
ORDER BY (valueList from Verity key WHERE list = eventID)... basically the key list is ordered by score -- I want to order the second query in the same way. or am I dreaming too much? -----Original message----- From: [EMAIL PROTECTED] Date: Mon, 5 Jul 2004 12:16:18 +0100 To: [EMAIL PROTECTED] Subject: Re: [ cf-dev ] QoQ and Verity > > not entirely sure what you're trying to do here, but what I've done in the > past with things like this is firstly perform your Verity search on the > keywords: > > <cfsearch name="getStuff" ... > > > then do a query on whatever criteria, plus your list of values returned > from the cfsearch. > > <cfquery name="dosearch" datasource="dsn"> > SELECT columns > FROM tableName > WHERE [some criteria] > <cfif getStuff.RecordCount> AND ID IN (#ValueList(getStuff.Key) > #)</cfif> > </cfquery> > > don't think you can treat the results from the CFSearch as a table in a > second query though. > > > It might be that you'd be better off adding the relevant information > (eventTitle, eventAims) to the actual collection (probably using the > Custom1 and Custom2 fields). > > > > > > > damian > > <[EMAIL PROTECTED] To: <[EMAIL PROTECTED]> > > ign.co.uk> cc: > > Subject: [ cf-dev ] QoQ and > Verity > 05/07/2004 11:43 > > Please respond to dev > > > > > > > > > Hi all, > > I've got a verity search resultset and an additional resultset that I'm > trying to join with a QoQ -- however it seems that CF don't like doing > that- I'm getting errors like "Incorrect Select list" for the verity key > column -- anyone know what I need to do or what I'm doing wrong? Here's the > code: > > <cfsearch collection="eventsMatrix" type="SIMPLE" criteria=' > (#request.s_searchString#) OR #replace(form.searchKeywords,' ', ', ', > "ALL")#' name="keywordSearch"> > <cfquery name="selectEventsAdvanced" datasource="#request.dsn#"> > SELECT eventID,eventTitle,eventAims > FROM contentEvents > WHERE > <cfif isDate(form.dateFrom)>dateFrom > >= #form.dateFrom#</cfif> > </cfquery> > <cfquery name="selectJoinResults" dbtype="query"> > SELECT > keywordSearch.score, > > > selectEventsAdvanced.eventID,selectEventsAdvanced.eventTitle,selectEventsAdvanced.eventAims > > FROM keywordSearch, selectEventsAdvanced > WHERE selectEventsAdvanced.eventID = > keywordSearch.key > ORDER BY keywordSearch.score > </cfquery> > > Cheers > d > > -- > These lists are syncronised with the CFDeveloper forum at > http://forum.cfdeveloper.co.uk/ > Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > CFDeveloper Sponsors and contributors:- > *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided > by activepdf.com* > *Forums provided by fusetalk.com* :: *ProWorkFlow provided by > proworkflow.com* > *Tutorials provided by helmguru.com* :: *Lists hosted by > gradwell.com* > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > > > > > -- > These lists are syncronised with the CFDeveloper forum at > http://forum.cfdeveloper.co.uk/ > Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > CFDeveloper Sponsors and contributors:- > *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by > activepdf.com* > *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* > *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* > > To unsubscribe, e-mail: [EMAIL PROTECTED] > -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED]
