Using Query Results in Queries

ColdFusion allows you to use the results of a previous query in any cfquery tag that returns row data to ColdFusion. You can query a database once and use the results in several dependent queries. Queries generated from other queries are often referred to as query of queries.

Query of query benefits

Performing queries on query results has many benefits:

If you need to access the same tables multiple times, you greatly reduce access time for tables with up to 10,000 rows because the data is already in memory.
You can join and perform unions on results from different data sources.
For example, you can do a union on queries from different databases to eliminate duplicates for a mailing list.
You can efficiently manipulate cached query results in different ways. You can query a database once, and then use the results to generate several different summary tables.
For example, if you need to summarize the total salary by department, by skill, and job, you can make one query to the database and use its results in three separate queries to generate the summaries.
You can make drill-down, master-detail-like functionality where you do not go to the database for the details.
For example, you can select information about departments and employees in a query and cache the results. You can then display the employee names. When users select an employee, the application displays the employee details by selecting information from the cached query without accessing the database.

Creating queries of queries

You can create a query using a query object from any ColdFusion tag or function that generates query results, including cfldap, cfdirectory, chttp, cfstoredproc, cfpop, cfindex, and the Query functions.

You can use a limited subset of the SQL SELECT syntax, which includes:

FROM

WHERE

GROUP BY

UNION

ORDER BY

HAVING

AS

DISTINCT

Boolean predicates:
LIKE NOT LIKE IN NOT IN BETWEEN NOT BETWEEN AND OR
Aggregate functions:
Count([DISTINCT][*] expr) Sum([DISTINCT] expr) Avg([DISTINCT] expr) Max(expr) Min(expr)
You cannot nest aggregate functions.

Comparison operators:
<= >= = < > <>

FROM	WHERE
GROUP BY	UNION
ORDER BY	HAVING
AS	DISTINCT
Boolean predicates: LIKE NOT LIKE IN NOT IN BETWEEN NOT BETWEEN AND OR	Aggregate functions: Count([DISTINCT][*] expr) Sum([DISTINCT] expr) Avg([DISTINCT] expr) Max(expr) Min(expr) You cannot nest aggregate functions.
Comparison operators: <= >= = < > <>

You can also do the following tasks:

Use the results of one or two queries in your SQL statement.
Generate computed columns.

Performing a query on a query

To generate a query using an existing query:

Specify the cfquery tag's dbtype attribute as "query".
Do not specify a datasource attribute.
Specify the names of one or more existing queries as the table names in a SQL SELECT statement.
If the database content does not change rapidly, it is a good idea to use the cachedwithin attribute to cache the query results of 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. Note that you must use the CreateTimeSpan function to specify the cachedwithin attribute value.

Note

You cannot add a literal value as a column to the SELECT list in a query of queries.

Your query generates a new query results set, identified by the value of the name attribute. The following example illustrates the use of a master query and a single detail query that extracts information from the master. A more extended example would use multiple detail queries to get different information from the same master query.

To use the results of a query in a query:

Create a new application page in ColdFusion Studio.

Edit the page so that it appears as follows:

<html>

<head>

<title>Using Query Results in a Query</title>

</head>

<body>

<h1>Employee List</h1>

<!--- LastNameSearch normally would be generated interactively --->

<cfset LastNameSearch = "Jones">

<!--- Normal query --->

<cfquery datasource = "CompanyInfo" name = "EmpList"

cachedwithin=#CreateTimeSpan(0,1,0,0)#>

  SELECT * 

  FROM Employee

</cfquery>



<!--- Query using query results --->

<cfquery dbtype = "query" name = "QueryFromQuery" > 

  SELECT Emp_ID, FirstName, LastName

  FROM EmpList

  WHERE LastName = '#LastNameSearch#'

</cfquery>



Output using a query of query<br>

<cfoutput query = QueryFromQuery>

  #Emp_ID#: #FirstName# #LastName#<br>

</cfoutput>

<br>



Columns in the Emplist database query<br>

<cfoutput>

  #Emplist.columnlist#<br>

</cfoutput>

<br>



Columns in the QueryFromQuery query<br>

<cfoutput>

  #QueryFromQuery.columnlist#<br>

</cfoutput>

</body>

</html>

Save the page as queryquery.cfm in myapps under the Web root directory.
Return to your browser and enter the following URL to view the results of the query:
http://127.0.0.1/myapps/queryquery.cfm
View the source in the browser.

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.

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.

Developing ColdFusion Applications
Querying a Database