Timothy has the right method, but just a note: QoQ will throw a syntax error unless you use a quoted value list on the IN ()
WHERE query2.primary_key NOT IN (#quotedValueList(query1.primary_key)#) HTH, Jon On Apr 5, 2007, at 10:18 AM, Heald, Timothy J wrote: > Do a value list of the primary key in query one: > > <cfset myValues = valueList(query1.myValues)> > > The use it in an in() in the query of queries of query2: > > <cfquery name="filterQuery" dbtype="query"> > Select * > From query2 > Where query2.primaryKey not in(#myValues3 > </cfquery> > > I'm sure it's not exact, but it should get you close enough. > -----Original Message----- > From: Peterson, Chris [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 05, 2007 9:59 AM > To: CF-Talk > Subject: QoQ - remove all instances of value from Query1 in Query2? > > I have 2 Query objects. I need to remove all records from query 1 > that > have a matching value in query 2, so I basically need to end up with a > query result set that has the contents of query 1 less the contents of > query 2. Any idea's, other than a very manual loop with a check on > each > value individually? If this was SQL I would do a WHERE username > NOT IN > (select username from Query2), but that does not work with QoQ. > > > Anyone know some tricks? > > Chris Peterson > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274633 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
