• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Working with Databases > Querying Databases with SQL

Querying Databases with SQL

Although storing information is very useful, the true power of databases comes from their capabilities to accept queries and return specific records in a certain order. Queries consist of a communication sent from a program to a database that specifies how a database should search its records. The de facto query language supported by almost every database vendor is Structured Query Language (SQL). The database takes the results of the query and creates a recordset, which contains all the records that match the criteria of the SQL query, and passes the recordset back to the program that requested the query.

Understanding SQL

SQL is a query language that uses basic statement syntax. In general, SQL statements consist of the following types:

  • SELECT— List of one or more table column names. Must be used with a FROM statement.

  • FROM— List of one or more table names. Must be used with a SELECT statement.

  • WHERE— A comparison statement to filter the query created in the SELECT and FROM statements.

  • ORDER BY— List of one or more column names to sort by. You can also use ASC (ascending) or DESC (descending) to specify which way to sort the records.

You use SELECT and FROM statements to specify one or more table columns from one or more database tables. The following example selects the FirstName column from the tblEmployees table:

<cfquery name="Recordset1" datasource="exampleapps"> 
    SELECT FirstName 
    FROM tblEmployees 

As you can see, SQL can be very simple. To select more than one table column, simply add a comma and the column name, as the following example shows:

<cfquery name="Recordset2" datasource="exampleapps"> 
    SELECT FirstName, LastName, Email 
    FROM tblEmployees 

To select all columns in the tblEmployees table, use the asterisk (*), as the following example shows:

<cfquery name="Recordset3" datasource="exampleapps"> 
    SELECT * 
    FROM tblEmployees 

To filter a query, you use the WHERE statement to filter the records using a comparison statement, such as comparing a record to a value or another record.

The following example compares the ItemCost column to a numeric value:

<cfquery name="Recordset3" datasource="exampleapps"> 
    SELECT ItemCost, ItemName 
    FROM tblItems 
    WHERE ItemCost > 500 

If the value in an ItemCost column cell is greater than the value, the record is kept. If not, the record is removed. If you want to compare against text, you use single quotes (‘’) to surround the comparison value, such as the following example:

<cfquery name="Recordset5" datasource="exampleapps"> 
    SELECT LastName 
    FROM tblEmployees 
    WHERE LastName = 'Moon' 

If you want to compare column values in one table to column values in another, you simply add the table name to the FROM statement and preface the column names in the SELECT statement with their table name, as the following example shows:

<cfquery name="Recordset6" datasource="exampleapps"> 
    SELECT tblEmployees.FirstName, tblEmployees.LastName, 
    tblEmployees.Email, tblEmployees.DeptIDFK, tblDepartments.DepartmentID, 
    FROM tblEmployees, tblDepartments 
    WHERE tblEmployees.DeptIDFK = tblDepartments.DepartmentID 
    ORDER BY tblEmployees.LastName ASC 


This SQL statement associates a department name with each employee. The tblEmployees table does not contain the department name, but does include a foreign key to the tblDepartments column. By prefacing the column names in the SELECT statement with the table name, you identify the column with a particular table, thereby avoiding errors.

The WHERE clause checks for equivalence between the DeptIDFK column in the tblEmployees table, which contains foreign keys, to the DepartmentID column in the tblDepartments table. Notice that the LastName column is sorted by the ORDER BY statement.

For more information on displaying or manipulating recordsets in ColdFusion pages, see Chapter 6, “Creating Pages with Dynamic Elements” and Chapter 8, “Displaying Records in a Dynamic Table.”


All SQL contains SELECT and FROM statements, but you can also use other programming language constructs, such as the AND, OR, and EXISTS operators, or evaluation precedence.

Using Dreamweaver to Build Query Statements

In ColdFusion, you use SQL inside a CFQUERY statement. The CFQUERY tag supplies a name for the recordset created by the SQL statement as well as the ColdFusion data source name for the query. As the following example shows, the CFQUERY tag encapsulates your SQL:

<cfquery name="lastNameQuery" datasource="exampleapps"> 
    SELECT LastName 
    FROM tblEmployees 

As you can see, the CFQUERY tag creates a name for the recordset created by the SQL, lastNameQuery, and specifies the database to use, exampleapps.

In Dreamweaver MX, you use the Recordset dialog box, accessible from the Bindings or Server Behaviors panel in the Application panel group, to create SQL queries. To open the Recordset dialog box, click the plus (+) button in the Bindings or Database panel. In the submenu that appears, select Recordset (Query).

In the Recordset dialog box, shown in Figure 4.10, you build the query using a set of menus and text boxes. In the Name text box, you specify the name for the recordset, which translates to the name attribute of the CFQUERY tag. In the Data Source menu, you select the ColdFusion data source to use, which translates to the datasource attribute of the CFQUERY tag. If required, the User Name and Password text boxes let you enter security credentials to the database.

Figure 4.10. The Recordset dialog box lets you create SQL queries using menus and text boxes. You’ll notice that what you select in one control dynamically changes the values in other controls.

For more information on displaying recordsets in ColdFusion pages, see Chapter 6.

In the Table menu, you select the table to query, which translates to the FROM SQL statement. In the Columns section, you can select some or all of the columns in a table, which translates to the SELECT SQL statement. The Filter menus and text boxes let you construct simple WHERE statements, and the Sort menu lets you specify a column to sort by.

To test the SQL before you create the recordset, click the Test button. In the Test SQL Statement dialog box, shown in Figure 4.11, Dreamweaver runs the query and displays the results. This feature is especially helpful when developing more complex SQL statements.

Figure 4.11. The Test SQL Statement dialog box displays the results of your SQL, letting you quickly edit and test SQL statements during development.

To create queries that use multiple tables, you must use the Advanced Recordset dialog box. To switch to the Advanced layout, click the Advanced button in the Recordset dialog box.

In the Advanced Recordset dialog box , shown in Figure 4.12, you can write your SQL by hand in the SQL text box, or you can use the Database Items section and the SELECT, WHERE, and ORDER BY buttons to build the SQL visually.

Figure 4.12. The Recordset dialog in Advanced mode provides controls for writing SQL by hand or building SQL visually using the Database Items tree control and the WHERE, SELECT, and ORDER BY buttons.

You can also specify page parameters in your SQL. Parameters are variables passed from external sources, such as other ColdFusion pages, Flash movies, application and session scope variables, and so on. For more information about passing parameters, see Chapter 6.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint