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" <[email protected]>
Sent: Monday, January 31, 2011 2:42 PM
To: "cf-talk" <[email protected]>
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
