category_desc’ Stores categories that speakers can be listed under. This table is tied to. the speaker_category table through the speaker _category_ i d field. These tables are all related through primary keys and foreign keys. Once you have opened the sybex. mdb file In Access you can see the table relationships by selecting Tools/’Relationships on the Access menu bar. Also, refer back to Figure 10,1. For more information on creating data.’bases. table relationships, and primary and’ foreign keys please read if CreatinJ .
‘ Database and Tables.
in this CFQUERY tag, three attributes are specified. The NAME of the query, gee speaker_books, can be used later in the template in conjunction with the CFOUT’UT tag to output the query results. The name of the DATASOURCE that has been set up is the ColdFusion Administrator, sybex, points ColdFusion to the correct database fom
which to retrieve records. Moreover, the type of database driver that you are connecting to, ODBC, is a driver used with Access 97, the ‘database that has been includedon the accompanying CD-ROM. The.SQL statement that is included is a SELECT staement. In the next section, you will learn more about SQL SELECT statements
Retrieving Records from a Database
When data is retrieved from a database a SELECT SQL statement should used. To do this, you must specify in SQL which database tables and fields you woull like to retrieve information from. Additionally, you can specify filtering criteriato narrow your search of the database records, and you can sort and group your data. Tie following sections will discuss some of the SQL keywords and clauses that can be ued in a SELECT statement..
The first part of the SELECT statement specifies the fields that you want returned. This is a required part of the SELECT statemen’t. You begin the SELECT dause with the SELECT keyword followed by the names of the table fields you wish to b returned. If you are specifying more than one field, you must separate each field nane with a comma. It is also a good idea to precede the field name with the table narneprefix. This way, if you are retrieving data from more than one table, it will be easy to determine whlat table the fields come from. The code would be written as follows:
If you are selecting all columns, or fields in a table, then you can use the * symbol. This symbol is a wildcard that will retrieve all fields, thereby saving you from having to type out each field name. You should only use this symbol, as follows, if you want to retrieve all fields, as it puts more strain on the database connection.
You can also us.e the DISTINCT keyword to suppress the display of duplicate records where all fields in a’row are the same. This might occur, for example, if you want to . select all speakers that have written books, but do not want to see each book. Without DISTINCT, a speaker may be returned several times if they have written more than one book. The DISTINCT keyword, if used, would be placed immediately after the SELECT keyword and before any fields that you are selecting, as shown in the following code:
SELECT DISTINCT table_name.field_namel. table_name.field_name2
Some databases use the keyword DISTINCTROW instead .
Listing 10.1 shows an example of selecting one, several, and all fields of a particular table. WK’enused in a ColdFusion template these SQL statements should be included inside a CFQUERY tag. You will see examples of this later in the chapter.
The second part of your SELECT statement is wh~.re you define the table in which these fields are located. This FROMclause is also a required part of your SELECT statement. You begin this clause with the FROMkeyword followed by the namets) of the table(s) that you are selecting from. If you are selecting fields from more than one table; you should , rate the table names with a comma.
Adding the FROM Keyword
You are not required to add any more clauses to your SC2LSELECT statement in order for it to work. You must realize, though, that with queries like those in Listing 10.2 all records will be returned. If you only want certain records to be returned, you must add a WHERE clause to your query. The WHERE clause allows you to define filtering criteria when selecting records from the database.
You begin a WHERE clause with the WHERE keyword followed by one or more conditions. If there is morethan one condition then they need to be connected using the . AND, OR, or Nor operators. All conditions included in the WHERE clause must evaluate to true or false. Listing 10.3 shows various examples of WHERE clauses and SC2Lconditions: SC2Lconditions and operators will be covered in depth later in this chapter. Results for these queries cannot yet be shown, because you need to include the queries inside of a CFQUERY tag. Results of example queries will be shown when we discuss the CFOUtPUt’ tag later in this chapter.
The last example in Listing 10.3 shows how to join two tables together on the primary
and foreign key fields of each table. This is one way that you can join tables. You can see . visually how the two tables are joined together.
•The speaker Id field in the speakers table is tied to the speaker field in the speaker_books table so that you can determine which books in the speaker_books table were written by which speakers in the speakers table. If the code speakers . speaker speaker _books. speaker was not included in the statement, then the results would give every possible combination of rows in the speakers table’ matched to every row in the speaker_books table. This could amount to an extremely. large number if your tables are quite large, and it could significantly decrease the speed and efficiency of your query. this is known as a Cartesian join. Besides, it most likely will not give you the results you are looking for.
Therefore, you need to learn how to join your tables on a related field. You can join tables as shown above, or’ you can learn more about joining in Chapter 11, “Using Advanced Query Techniques.”
SQL commands and keywords are not case sensitive, which means you can use upper case, lowercase, or mixed terms. However, case sensitivity is relevant for most databases when specifying data. For instance, the column names Test.Co 1urnn and testeo turn are the same, but using WHERETestCol urnn = ‘Hello’ is different from WHERETestCol turn ‘. ‘HELLO’.
It should also be mentioned that when strings and numbers are used in expressions, you need to be careful about the use of quotes. Strings should always be surrounded by single quotes. Numbers should not be surrounded by quofes at all, unless the field you _ are querying in the database is a text field. You C<1;I\ find more information on these topics in Chapter 11, “Using Advanced Query Techniques
The optional sorting of query results is achieved using the ORDERBY clause. ORDERBY I allows you to specify which fields you would like to sort and whether the results should be sorted in ascending or descending’ order. The ORDERBY clause is also used in coniunction with the CFOUTPUTGROUPattribute to organize the query results into groups. CFOUTPUT be discussed later in this chapter and in detail in Chapter .12,”Grouping, Nesting, and Formatting Outputs.”
To use ORDERBY, you must first enter the keywords ORDERBY followed by the field-namets) you wish to sort. The default sort order is ascending. If you would like to sort the – field as descending, you must add a space and then dese after the fieldname. Listing 10.4 shows various examples of ORDERBY clauses. Figure 10.3 shows the last query example ‘in Listing 10.4 being constructed using Studio’s Query Builder.
GROUP BY and Aggregate Functions
GROUP BY is used to organize related information together into groups, but is more often used in conjunction with aggregate functions. The GROUP BY clause should always’ be placed before the ORDER BY clause; if one is entered, and after the FROMand WHERE. clauses. You cannot use the SELECT * wildcard when using GROUP BY in your query. Aggregate functions allow you to apply certain functions to the columns retrieved in a query (the-columns specified in the SELECT list) and obtain an average result for that column. When using these functions in your SELECT list you must always place all additional fields contained in your SELECT statement (that are not also aggregate functions) into the GROUP BY clause (as shown in Listing 10.5 and Figure 10.4). Several of the aggregate functions are listed Table 10.2. An aggregate function takes the format: FUNCTION_NAME table_name.fie’d_name) as aliased_name.
When an aggregate function is applied to a fieldname in tlkeSELEa statement, you must give an alias to that field name so that CokiFusion knowshow it should be referenced. To do that, follow the function with the as operator am fuen the name to be applied to the result. When referencing the result later in the· b5mplate, refer to the aliased name. You can also use the following syntax for datal bases that will not work with the as operator:
When you use a GROUP BYclause and you want to filter the .results, you should use HAVING instead of WHERE. While HAVING and WHERE can be Used together in some situations, WHERE is intended to filter the records before they are grouped, and HAVING is intended to filter records after the grouping has been applied. HAVING should not be. used if GROUP BYis not included in the statement. If you are trying to use HAVING, GROUP BY,and ORDER BY,or WHERE all in the same SQL statement, you may want to familiarize yourself with the complexities of SQL first by purchasing a good SQL book. When HAVING is used with GROUP BY it should be placed after the GROUP BYclause,like the following: