Combining Query Results ColdFusion Help

There may be cases where you need to merge the result set from one query with the result set of one or more different queries. There are three set operators that can be used to combine query result sets (see Table 11.2)

TAB LE 1 1 . 2:

TAB LE 1 1 . 2:

In a UNION, the queries can pull data from any table as long as the SELECT list of each query contains the same number of fields, in the same order, and with the same data type. When you reference your query results in a CFOUTPUT block, you should refer to the field names in the first query. If you specify the field names from any other query in the UNION, ColdFusion will throw an error.

The query shown in Listing 11.1 actually gives the results that a FULL OUTER JOIN would give. The results can be seen in Figure 11.11. If your version of SQL does not support a FULL OUTER JOIN, you can use this method instead. The first query returns all records from speaker _books and only those records that match in speakers. The second query returns all records from speakers and only those records that match in speaker_books. The final result is that all records from each table are returned with NULL values in fields that do not match. This is done because the database removes redundant data. If you want every row from each query returned regardless of whether it is a duplicate, use the UNION ALL operator instead.

Listing 11.1: The UNION Operator
<CFQUERY NAME-‘get_speaker_books’ DATASOURCE3″sybex’>
SELECT speakers.speaker_id,
speakers.speaker_first_name,
speakers. speaker_l ast_name ,
speaker_books.book_title,
speaker_books.book_price
FROM speakers RIGHT JOIN speaker_books
ON speakers.speaker_id a speaker_books.speaker_id
UNION
SELECT speakers.speaker_id,
speakers.speaker_first_name,
speakers.speaker_last_name,
speaker_books.book_title,
speaker_books. book-pri ce
FROM speakers LEFT JOIN speaker_books
ONspeakers.speaker_id – speaker_books.speaker_id
ORDER BY speakers.speaker_last_name desc,
speaker_books. book_title
</CFQUERY>
<!DOCTYPE HTML.PUBLIC ‘-//W3C//DTD HTML 4.0
Transitional//EN’>
<HTML><HEAD><TITLE>Sybex – Mastering ColdFusion 4.5-
Chapter 11 – Listing 11.1</TITLE></HEAD>
<BODY BGCOLOR-‘#FFFFFF’>
<Hl>Speakers & Books</Hl>
<TABLE BORDER- ‘1’ CELLPADDING-‘ 2·’ CELLSPACING-‘ 0 ‘>
<TR>
<TD><B>Speaker Name</B></TD>
<TD><B>Book Title & Price</B></TD>
</TR>
<CFOUTPUT QUERY-‘get_speaker_books’
GROUP-‘speaker_last_name’>
<TR VALIGN-‘top’>
<TD>&nbsp;<B>#speaker_first_name#
#speaker_last_name#</B></TO>
<TO>
<CFOUTPUT>
#book_title#
<CFIF book_price IS NOT “>
– #OollarFormatCbook_price)#
<fCFIF>
<BR>
</CFOUTPUT>
</TO>
</TR>
</CFOUTPUT>
</TABLE>
<fBOOY>
</HTML>

FIGURE 11.11

FIGURE 11.11

When using the ORDER BY clause, you can sort the results of both queries together. The ORDER BY clause only needs to be used on the second query.

Some things to consider before choosing the UNION operator

• You cannot use DISTINCT in any of your query SELECT clauses. Because duplicates are removed anyway, it is unnecessary.
• Fields with the data type of memo, image, or any other BLOB type cannot be used in a UNION.
• You can only use an INTO clause in the first query.
• GROUP BY and HAVING clauses cannot be applied to all results. They can only be used on each individual query.

Creating and Changing Tables via SQL

In some cases, developers do not have easy access to the database that their Web Application connects to. In such a situation one may think that it would be impossible for a developer to add modify or delete tables and columns in a database. Quite the contrary. You can add modify and delete tables and columns from a database using the CREATE SELECT INTO ALTER and DROP clauses in a CFQUERY SQL statement.

Creating Tables

The CREATE clause can be used to create a variety of different database objects including tables columns views procedures triggers etc. In this section you will learn how to create empty tables and columns

To create a new table begin your SQL statement with the CREATE TABLE keywords followed by the name of the new table. Table names must follow these three attributes:

• They cannot begin with numbers
• They cannot include illegal characters
• They must be unique

You should consult your database documentation for what table-naming conventions are supported. After adding the table name specify the columns you would like to add. First specify the column name then its data type and length and then any constraints that you may want to add to the column. The syntax for creating a new table is CREATE TABLE table_name (column_name data_type[(length)] [constraint] [ … ]).when brackets are used in the syntax it indicates an optional element.

The column name should be unique and follow the naming conventions as described in your database documentation.

The data type is used to define what kind of data the column will be storing. If you specify the incorrect data type, you will have to drop the column and add it again. You cannot modify the columns properties once set. Some databases may only allow you to add certain data type columns when the table is first created. Consult your database documentation for additional information

The length of a column can be specified depending upon data type. If it is not necessary just remove that section of the query. The constraint portion is also purely optional. A constraint is a rule that you are applying to the new column. Various constraints that you can apply to a column are listed in Table 11.3.

TAB LE 1 1  3:

TAB LE 1 1 3:

When a column is defined, a constraint can be applied to it directly or it may be applied at the end of the column definition in a CONSTRAINT clause. Listing 11.2 shows the creation of an Access table. The field data types will vary depending upon your version of SQL. For example, the data type COUNTER would be IDENTITY in SQL Server. In Listing 11.2 various data type columns are created. Then the primary key value is applied to two columns using the CONSTRAINT clause. In the CONSTRAINT clause, customer _pk is the name given to the primary key. This is required in order to create the primary key. If you specify the primary key in a column definition you do not need to give the primary key a name. When Listing 11.2 is run through the browser, you will get results like those shown in Figure 11.12. The resulting table in Access will look like Figure 11.13.

<CFQUERY NAME-‘new_table’ DATASOURCE-‘sybex’>
CREATE TABLE customers (
customer_id counter,
cust~r_name varchar(SO) NOT NULL,
customer_age smallint,
customer_income currency NOT NULL,
customer_comments memo,
date_modified timestamp,
customer_kids logical,
CONSTRAINT customers-pk PRIMARY KEY
(customer_id, customer_name)
)
</CFQUERY>
<!OOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0
Transitional//EN’>
<HTML><HEAD><TITLE>Sybex – Mastering ColdFusion 4.5-
Chapter 11 – Listing 11.2</TITLE></HEAD>
<BODY BGCOLOR-“FFFFFF’>
<Hl>Customers Table Created!</Hl>
</BODY>
</HTML>

FIGURE 11.12

FIGURE 11.12

FIG U R'E 11.13

FIG U R’E 11.13

You can also create indexes using the CREATE clause. The syntax is: CREATE [UNIQUE] INDEX index_name ON table_name (column_list).

SELECT INTO

You can also use SELECT INTO to create tables populated with data pulled from a SELECT statement. The syntax for a SELECT INTO query is the following:

SELECT column_list
INTO new_table_name
.fROM table_list
WHERE search_criteria

The new table name must be unique and follow the table-naming conventions for your database. The column names for the new table will be the same as the columns pulled from the select list

Listing 11.3 creates a new table called doctors and populates it with data pulled from the speakers table. Results are shown in Figures 11.14 and 11.15.

<CFQUERY NAME-‘new_table’ DATASOURCE-‘sybex’>
SELECT speaker_first_name,
speaker_last_name,
speaker_picture
INTO doctors
FROM speakers
WHERE speakers.speaker_first_name LIKE ‘dr.%’
</CFQUERY>
<!DOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0
Transitional//EN’>
<HTML><HEAD><TITLE>Sybex – Mastering ColdFusion 4,5-
Chapter 11 – Listing 11.3</TITLE></HEAD>
<BODY BGCOLOR-‘IFFFFFF’>
<Hl>Doctors Table Created and Data Inserted!</Hl>
</BODY>
</HTML>

FIGURE 11.14

FIGURE 11.14

FIGURE 11.15

FIGURE 11.15

 

Posted on November 16, 2015 in Using Advance Query Techniques

Share the Story

Back to Top
Share This