Re: Query of query with cfdirectory
Thank you. With the help of a friend we've now come up with this which seems to do the job: SELECT myslide from slides SELECT * FROM uploads WHERE uploads.name not in (#QuotedValueList(getSlides.myslide)#) Stefan On 17 Nov 2009, at 16:31, b...@bradwood.com wrote: > > 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 > Date: Tue, November 17, 2009 10:13 am > To: cf-talk > > > 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:328473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of query with cfdirectory
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 Date: Tue, November 17, 2009 10:13 am To: cf-talk 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
Query of query with cfdirectory
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:328465 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
