User Guide

34 Chapter 3 Querying a Database
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