Stefan,
This might work ... can't remember if the IN () function works in QoQ or not. <!--- get a list of all the rooms from the DB (after you query) ---> <cfset roomList = valueList(roomsDB.roomid) /> <!--- now query to get folders that are not in the rooms DB list ---> <cfquery dbtype="query" name="roomsOK"> SELECT * FROM roomList WHERE name NOT IN ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#roomList#" list="true" /> ) </cfquery> ---------------------------------------- From: "Stefan Richter" <ste...@flashcomguru.com> Sent: Monday, January 31, 2011 2:42 PM To: "cf-talk" <cf-talk@houseoffusion.com> Subject: Re: Query of query help Thanks. Guess what I was trying to avoid was a loop with 35,000 queries... I was trying to come up wit a clever way to do this in one SQL statement. One issue I noticed is that QoQ does not to seem to (easily?) support joins. Then again I'm no SQL ninja by any means. S On 31 Jan 2011, at 17:54, Jason Fisher wrote: > > Yes, QoQ will work here. > > > <!--- get your folders ---> > <cfdirectory action="list" name="roomList" type="dir" > directory="#roomsDir#"> > > <!--- get the rooms from the DB ---> > <cfquery datasource="#dsn#" name="roomsDB"> > SELECT roomid > FROM rooms > </cfquery> > > <cfoutput query="roomList"> > <!--- loop through the folders and check against the rooms in the DB ---> > <cfquery dbtype="query" name="orphanRoom"> > SELECT name > FROM roomsDB > WHERE roomid = '#name#' > </cfquery> > <cfif not orphanRoom.recordCount> > <!--- flag this as a room without a folder ---> > </cfif> > </cfoutput> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341735 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm