User Guide

Table Of Contents
492 Chapter 22: Using Query of Queries
Performing a Query of Queries
There are four steps to perform a Query of Queries.
To perform a Query of Queries:
1.
Generate a record set through a master query.
You can write a master query using a tag or function that creates a record set. For more
information, see “Creating a record set” on page 489.
2.
Write a detail query—a cfquery tag that specifies dbtype="query".
3.
In the detail query, write a SQL statement that retrieves the relevant records. Specify the names
of one or more existing queries as the table names in your SQL code. Do not specify a
datasource attribute.
4.
If the database content does not change rapidly, use the cachedwithin attribute of the master
query to cache the query results between page requests. This way, ColdFusion accesses the
database on the first page request, and does not query the database again until the specified time
expires. You must use the
CreateTimeSpan function to specify the cachedwithin attribute
value (in days, hours, minutes, seconds format).
The detail query generates a new query result set, identified by the value of the
name attribute of
the detail query. The following example illustrates the use of a master query and a single detail
query that extracts information from the master.
To use the results of a query in a query:
1.
Create a ColdFusion page with the following content:
<h1>Employee List</h1>
<!--- LastNameSearch (normally generated interactively) --->
<cfset LastNameSearch="Doe">
<!--- Master Query --->
<cfquery datasource="cfdocexamples" name="master"
cachedwithin=#CreateTimeSpan(0,1,0,0)#>
SELECT * from Employee
</cfquery>
<!--- Detail Query (dbtype=query, no data source) --->
<cfquery dbtype="query" name="detail">
SELECT Emp_ID, FirstName, LastName
FROM master
WHERE LastName=<cfqueryparam value="#LastNameSearch#"
cfsqltype="cf_sql_char" maxLength="20"></cfquery>
<!--- output the detail query results --->
<p>Output using a query of query:</p>
<cfoutput query=detail>
#Emp_ID#: #FirstName# #LastName#<br>
</cfoutput>
<p>Columns in the master query:</p>
<cfoutput>
#master.columnlist#<br>