RE: [CFTALKTor] how to nest loop the database query?

[charles arehart]

Mike, it seems that what you're trying to do (list members by category) can be done more easily by way of the CFOUTPUT GROUP capability.  Try this:   <CFQUERY NAME="FIRST" DATASOURCE="#DSN#" USERNAME="#DB_USER#" PASSWORD="#DB_PASSWORD#">  SELECT memer_id  FROM RRC_members  ORDER BY category </CFQUERY> <CFOUTPUT QUERY="FIRST" GROUP="Category">     <h2>#category#</h2>     <CFOUTPUT>           #member_id#<br>      </CFOUTPUT> </CFOUTPUT>   This will list each category (you weren't doing that, but it seems reasonable that you might want to so I'm showing how), and for each category list each member id. You only listed that before, so I changed the SELECT * to select just that. You ought to avoid Select * when you don't need all the data it would retrieve.   Note that the trick here is the ORDER BY category. That causes all the found records to be sorted in category order. Then the GROUP BY attribute in CFOUTPUT tells it, as its looping through them, to detect when each unique value is found. The "outer" CFOUTPUT will loop once per unique value of the GROUPed column. Then the inner CFOUTPUT (there is no QUERY attribute on it intentionally) will loop once for each record in the resultset for THAT unique value being processed. Very slick, if you've not seen it before.   And if you wanted to sort the member_ids with category, just change the ORDER BY to ORDER BY category, member_id.   /charlie   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mike Lau Sent: Thursday, March 28, 2002 5:05 PM To: [EMAIL PROTECTED] Subject: [CFTALKTor] how to nest loop the database query? Hi list;       I was trying to nest loop 2 queries.  However, after the first round of the FIRST query (the SECOND query finished its first execution), it returns error said that the query FIRST is not available.  Anyone here can help me to solve this problem please?   ============================================================== Error Diagnostic Information Cannot set default query to FIRST A query by this name is not available at this time The error occurred while processing an element with a general identifier of (CFOUTPUT), occupying document position (11:2) to (11:24). Date/Time: 03/28/02 16:55:28 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0; T312461) Remote Address: 207.255.255.182 ============================================================== here is the code:   <CFQUERY NAME="FIRST" DATASOURCE="#DSN#" USERNAME="#DB_USER#" PASSWORD="#DB_PASSWORD#">  SELECT DISTINCT Category  FROM RRC_members </CFQUERY> <CFLOOP QUERY="FIRST">      <CFQUERY NAME="SECOND" DATASOURCE="#DSN#" USERNAME="#DB_USER#" PASSWORD="#DB_PASSWORD#">       SELECT *       FROM RRC_members       WHERE category= '#FIRST.Category#'      </CFQUERY>      <CFOUTPUT QUERY="SECOND">                            <====================== line 11           #SECOND.member_id#<br>      </CFOUTPUT> </CFLOOP>     Rgds,   Mike