Share this Page URL

Chapter 19. Advanced SQL > Combining SQL Statements - Pg. 369

Advanced SQL 369 Combining SQL Statements Sometimes you'll want to write compound SQL statements that rely on one another to get something done. This section discusses using SELECT, INSERT, UPDATE, and DELETE statements together in various ways. It is generally unnecessary to use these statements in your ColdFusion applications because it is usually possible to get the same results using some kind of looping constructs in your ColdFusion templates. The techniques discussed in these sections can often get things done more efficiently. Making Multiple a SELECT Behave as One with UNION You can use SQL's UNION operator to combine the results from two different queries. Compared to joins and subqueries, UNION is pretty simple because it doesn't have anything to do with rela- tionships between tables. It's simply about combining the results of two different SELECT state- ments. The SELECTs can be based on the same table or on different tables. Say you are planning a holiday party and want to display the names of all employees and customers. You want them in alphabetical order by first name, which means that the employees will be freely intermixed with the customers. Listing 19.23 shows the code used to accomplish this, and Figure 19.17 shows the results. Example 19.23. EVERYONE.CFM-- Using UNION to Combine Two Result Sets <CFQUERY NAME="Report" DATASOURCE="A2Z"> SELECT FirstName, LastName, Phone FROM Customers UNION SELECT FirstName, LastName, PhoneExtension FROM Employees ORDER BY FirstName, LastName </CFQUERY> <HTML> <HEAD> <TITLE>Invitation List</TITLE> </HEAD> <BODY> <H1>Invitation List</H1> <CFOUTPUT QUERY="Report"> #FirstName# #LastName# - #Phone#<BR> </CFOUTPUT> </BODY> </HTML>