Try Googling "left outer join in a ColdFusion query of query". If you have the two result sets there, left outer the DB list to the directory list to find items in the directory for which there is no matching database record.
As for performance, depending on the size of the result sets (less than 5-6 thousand records), you might get just as much performance by simply looping over the directory list and doing a q of q for each item on the database list. Just try it see how it performs. If this is just a nightly job you might not even care how long it takes. ~Brad -------- Original Message -------- Subject: Query of query with cfdirectory From: Stefan Richter <ste...@flashcomguru.com> Date: Tue, November 17, 2009 10:13 am To: cf-talk <cf-talk@houseoffusion.com> Hi all, The task at hand is to find a series of orphaned folders. I want to delete all folders in a certain directory which do not have a matching DB record. I was wondering if this is possible (or accept any other tips): I have a 'query' which is the result of a cfdirectory action="list". I now would like to find any records in that query that do not exist in the resultset of a second query (this second one is a database query). Is there a way to combine these two queries to get a resultset that just holds those items which do not exist in the second query? I know i could loop and query each record but wondered if there's a more efficient way, maybe a query of two other queries? Cheers Stefan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328467 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
