Programming ColdFusion MX, 2nd Edition

Query of Queries

Now that you have an understanding of advanced SQL, it’s time to introduce a new concept known as “query of queries” or CFSQL (ColdFusion SQL). Introduced in ColdFusion 5.0 and greatly enhanced in MX, query of queries allows you to use the cfquery tag to query an already existing query object using a subset of ANSI SQL 92. This feature makes it easy to program functionality previously difficult or impossible to implement in ColdFusion. Some potential uses for query of queries include:

• Manipulate query objects (sort, summarize, group, etc.) returned by other ColdFusion tags such as cfhttp, cfftp, cfldap, cfpop, cfsearch, or cfstoredproc as well as the various query functions

• Perform joins and unions between tables from different data sources

• Resorting a query result set without having to go back to the data source

• Moving entire tables into memory (Macromedia recommends no more than 50,000 rows in ColdFusion MX or 10,000 rows in ColdFusion 5), effectively speeding up query times because ColdFusion no longer has to make a round trip to the database for each query performed

Regardless of what you decide to use this feature for, you need to know what SQL constructs are available. For starters, query of queries can only be used to perform an SQL SELECT. This means that query of queries can be used only to select records, not INSERT, UPDATE, or DELETE them. This makes sense, as the purpose of query of queries is to allow you to perform a query against an already existing ...