Query of query help

2011-01-31 Thread Stefan Richter
Hi all, hoping for some advice with querying a query - at least I think that's what's a good fit here but feel free to advise otherwise. I've got a folder which contains 35,000 folders. I've also got a table with lots of records, where the roomid column matches the name of one of the

Re: Query of query help

2011-01-31 Thread Raymond Camden
Um, did you try the where clause? You said it didn't work - how did it not work? Can you show us the full code and the error? On Mon, Jan 31, 2011 at 11:48 AM, Stefan Richter ste...@flashcomguru.com wrote: Hi all, hoping for some advice with querying a query - at least I think that's what's

re: Query of query help

2011-01-31 Thread Jason Fisher
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

Re: Query of query help

2011-01-31 Thread Dave Watts
I've got a folder which contains 35,000 folders. Setting aside your QoQ question, you may have problems simply traversing a folder with this many items in it. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small

Re: Query of query help

2011-01-31 Thread Stefan Richter
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

Re: Query of query help

2011-01-31 Thread Stefan Richter
Thanks Dave, listing the folders performs a lot better than I expected, so that's not my issue here. Cheers Stefan On 31 Jan 2011, at 17:57, Dave Watts wrote: I've got a folder which contains 35,000 folders. Setting aside your QoQ question, you may have problems simply traversing

Re: Query of query help

2011-01-31 Thread Jason Fisher
-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

Re: Query of query help

2011-01-31 Thread Stefan Richter
I was trying something like this: cfquery name=orphanRooms dbtype=query select * from roomList WHERE name = 'x' cfloop query=roomsDBOR roomList.name = '#roomid#' /cfloop /cfquery I think I am getting somewhere with it. Anything inherently wrong with this? Stefan On 31 Jan 2011, at

Re: Query of query help

2011-01-31 Thread Stefan Richter
Ah great, an interesting approach. Many thanks. Stefan On 31 Jan 2011, at 20:10, Jason Fisher wrote: 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 =

Re: Query of query help

2011-01-31 Thread Jason Fisher
... From: Stefan Richter ste...@flashcomguru.com Sent: Monday, January 31, 2011 3:21 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: Query of query help I was trying something like this: cfquery name=orphanRooms dbtype=query select * from roomList

query of query help

2005-02-21 Thread John Beynon
Given the following data in a query named qDevList DEVID PRICEFROM PRICETO 897 139950 244950 917 182500 465000 918 235000 525000 935 197000 445000 940 14 21 How can I pull a row that has price's between say

Re: query of query help

2005-02-21 Thread Qasim Rasheed
would this work? select * from qDevList where priceto between 40 and 60 or pricefrom between 40 and 60 On Mon, 21 Feb 2005 16:37:43 +, John Beynon [EMAIL PROTECTED] wrote: Given the following data in a query named qDevList DEVID PRICEFROM PRICETO

Re: query of query help

2005-02-21 Thread John Beynon
I think I might have found the problem...I have a row which was using TBC (to be confirmed) hence why I kept getting: Error Executing Database Query. brbQuery Of Queries runtime error./bbr Unsupported type comparison. I had the same SQL as you'd offered though, so that was a start! jb. would

Re: query on query help

2001-07-31 Thread Tim Painter
' will return the record. HTH, Tim P. - Original Message - From: Bruce, Rodney [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, July 30, 2001 7:20 PM Subject: query on query help Hello all I am just starting to work with trying to query a query. I am trying to count items

query on query help

2001-07-30 Thread Bruce, Rodney
Hello all I am just starting to work with trying to query a query. I am trying to count items and was wondering what I am doing wrong and why these two queries would give different results: CFQUERY name=query1 DBTYPE=qMain SELECT count (*) as total FROM qMain