User Guide

Using Query Results in Queries 37
Reviewing the code
The page retrieves the entire Employee table from the CompanyInfo database. A
second query selects only the three columns to display for employees with the
specified last name. The following table describes the code and its function:
Code Description
cfset ListNameSearch = "Jones"
Set the last name to use in the second
query. In a complete application, this
information comes from user
interaction.
<cfquery datasource = "CompanyInfo"
name = "EmpList"
cachedwithin=#CreateTimeSpan(0,1,0,0
)#>
SELECT *
FROM Employee
</cfquery>
Query the database specified in the
CompanyInfo data source and select all
data in the Employee table. Cache the
query data between requests to this
page, and do not query the database if
the cached data is less than an hour
old.
<cfquery dbtype = "query" name =
"QueryFromQuery" >
SELECT Emp_ID, FirstName,
LastName
FROM Emplist
WHERE LastName=’#LastNameSearch#’
</cfquery>
Use the EmpList query as the source of
the data in a new query. This query
selects only entries that match the last
name specified by the
LastNameSearch
variable. The query also selects only
three columns of data: employee ID,
first name, and last name.
<cfoutput query = QueryFromQuery>
#Emp_ID#: #FirstName#
#LastName#<br>
</cfoutput>
<br>
Use the QueryFromQuery query to
display the list of employee IDs, first
names, and last names.
<cfoutput>
#EmpList.columnlist#<br>
</cfoutput>
List all the columns returned by the
Emplist query.
<cfoutput>
#QueryFromQuery.columnlist#<br>
</cfoutput>
List all the columns returned by the
QueryFromQuery query.