Query a query is nice and simple, and doesn't have to get any data form the database, it simply filters/queries the currently retrieved recordset, so it's actually quite fast.
e.g. <!--- Random made upquery ---> <cfquery datasource="Whatever" name="AllData"> select * from table1 </cfquery> <cfoutput query="AllData" group="SomeColumn"> #SomeColumn# <cfoutput> <!--- Query of a Query, ---> <cfquery dbtype="query" name="GetCol1"> select col1 from AllData where col1 = #Alldata.variable1# </cfquery> <cfloop query="GetCol1"> #GetCol1.col1# </cfloop> </cfoutput> </cfoutput> That's totally off the top of my head, but you should get the picture? -----Original Message----- From: Paul Ihrig [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 2:03 PM To: CF-Community Subject: RE: Group & Loop Help! Craig.. i would love some help on this! i do have CF5 but haven't used query on a query my ugly code below Thanks -paul <cfquery name="Employees" datasource="RPH" dbtype="ODBC" debug> SELECT DISTINCT Left(tblEmployee.Lastname ,10) + ' ' + tblEmployee.Firstname as Fullname, tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID, tblEmployee.StudioID, tblEmployee.StatusID, tblStudio.StudioID, tblStudio.OfficeID, tblStudio.StudioName, tblOffice.OfficeID, tblOffice.City, tblPhone.PhoneNumb, tblPhone.PhoneTypeID, tblFloor.floor FROM tblEmployee, tblStudio, tblOffice, tblPhone, tblFloor WHERE tblEmployee.studioid = tblStudio.studioid AND tblOffice.OfficeID = '5' AND tblStudio.OfficeID = '5' AND tblPhone.EmpResID = tblEmployee.EmployeeID AND tblPhone.PhoneTypeID = '1' <!--- AND tblEmployee.StudioID in (#FORM.StudioID#) ---> AND tblEmployee.FloorID = tblFloor.floorid ORDER BY tblStudio.StudioName, Fullname;</cfquery> <!--- Get the number records returned ---> <cfset count_rows = Employees.recordcount> <cfset NumberOFColumns = 3> <cfset Row_start = 1> <cfset DivCol = (#count_rows# / #NumberOFColumns#)> <cfset Row_End = #NumberFormat(DivCol, "9999")#> <cfoutput>#count_rows# / #NumberOFColumns# = #NumberFormat(DivCol, "9999")# row end #Row_End#</cfoutput><br> <cfparam name="curSect" default="thefirm"> <html> <head> <title>NBBJ Intranet / The Firm / People / Phone List</title> <script language='javascript' src='/include/js.js' type='text/javascript'></script> <cfoutput> <script language='javascript' src='/include/layers_#curSect#.js' type='text/javascript'></script> </cfoutput> <link href="/include/ie.css" rel="stylesheet" type="text/css"> </head> <body bgcolor='#ffffff' topmargin='0' leftmargin='0' marginheight='0' marginwidth='0'> <table width="630" border="0" cellpadding="0" cellspacing="0"> <tr valign="top" align="left" bgcolor="#CCCCCC"> <td width="100" align="left" nowrap>Name</td> <td width="10" align="left" nowrap> </td> <td width="10" align="left" nowrap>Flr</td> <td width="10" align="left" nowrap> </td> <td width="80" align="left" nowrap>Number</td> <td width="100" align="left" nowrap>Name</td> <td width="10" align="left" nowrap> </td> <td width="10" align="left" nowrap>Flr</td> <td width="10" align="left" nowrap> </td> <td width="80" align="left" nowrap>Number</td> <td width="100" align="left" nowrap>Name</td> <td width="10" align="left" nowrap> </td> <td width="10" align="left" nowrap>Flr</td> <td width="10" align="left" nowrap> </td> <td width="80" align="left" nowrap>Number</td> </tr> <TR ALIGN="left" VALIGN="top"> <cfloop index="LoopNumber" from="1" to="#NumberOFColumns#"> <td colspan="15" valign="top" nowrap> <cfif LoopNumber eq NumberOfColumns> <cfloop query="Employees" startrow="#row_start#" endrow="#count_rows#"> <cfoutput query="Employees" group="StudioName"> <tr> <td colspan="15" align="left" valign="top" class="small"> #Employees.City# -#Trim(Employees.StudioName)# </td> </tr> <cfoutput group="Fullname"> <table width="210" border="0" cellspacing="0" cellpadding="0" class="small"> <tr align="left" valign="top"> <td width="100" align="left" nowraps class="small">#Employees.Fullname#</td> <td width="10" align="left" nowrap class="small"> </td> <td width="10" align="left" nowrap class="small">#Left(Employees.floor,2)#</td> <td width="10" align="left" nowrap class="small"> </td> <td width="80" align="left" nowrap class="small">#Trim(Employees.PhoneNumb)#</td> </tr> </table> </cfoutput></cfoutput> </cfloop> <cfelse> <cfloop query="Employees" startrow="#row_start#" endrow="#row_end#"> <!--- <cfoutput query="Employees" group="StudioName" startrow="#row_start#" maxrows="#count_rows#" groupcasesensitive="Yes"> <tr> <td colspan="15" align="left" valign="top" class="small"> #City# -#Trim(StudioName)# </td> </tr></cfoutput> ---> <cfoutput group="Fullname"> <table width="210" border="0" cellspacing="0" cellpadding="0" class="small"> <tr align="left" valign="top"> <td width="100" align="left" nowraps class="small">#Employees.Fullname#</td> <td width="10" align="left" nowrap class="small"> </td> <td width="10" align="left" nowrap class="small">#Left(Employees.floor,2)#</td> <td width="10" align="left" nowrap class="small"> </td> <td width="80" align="left" nowrap class="small">#Trim(Employees.PhoneNumb)#</td> </tr> </table> </cfoutput> </cfloop> <cfset row_start = row_start + #NumberFormat(DivCol, "9999")#> <cfset row_end = row_end + #NumberFormat(DivCol, "9999")#> </cfif> </td> </cfloop> </tr> </table> </body> </html> ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
