>From the CF5 docs...
============================================================================
===
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:
<=
>=
=
<
>
<>
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.
HTH,
Jeff Garza
Lead Developer/Webmaster
Spectrum Astro, Inc.
480.892.8200
[EMAIL PROTECTED]
http://www.spectrumastro.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
