MACROMEDIA COLDFUSION 5-DEVELOPING, Develop Manual

Page: 72 / 438

52 Chapter 4 Retrieving and Formatting Data
Creating table joins
Many times, the data that you want to retrieve is maintained in multiple tables. For
example, in the database that you are working with:
•
Department information is maintained in the Departmt table. This includes
department ID numbers.
•
Employee information is maintained in the Employee table. This also includes
department ID numbers.
To compare and retrieve data from more than one table during a query, use the
WHERE clause to join two tables through common information.
For example, to return employee names, start dates, department names, and salaries
for employees who work for the HR department, you build a query that looks like
this:
<cfquery name="GetEmployees" datasource="CompanyInfo">
SELECT Departmt.Dept_Name,
Employee.FirstName,
Employee.LastName,
Employee.StartDate,
Employee.Salary
FROM Departmt, Employee
WHERE Departmt.Dept_ID = Employee.Dept_ID
AND Departmt.Dept_Name = ’HR’
</cfquery>
In this example, the following criteria joins the two tables:
Departmt.Dept_ID = Employee.Dept_ID
It ensures that each row of the query results contains the department name from the
Departmt table that corresponds to the Department ID in this employee’s row in the
Employee table. Without this statement, the query returns a row for every employee
in the Employee table, and all rows have the Dept_Name HR, even if the employee is
not in the HR department.
When you do table joins, keep the following information in mind:
•
Prefix each column in the SELECT statement to explicitly state which table the
data should be retrieved from.
•
In this example, the Dept_ID field is the primary key of the Departmt table and
the foreign Key of the Employee table. A
foreign key uniquely identifies another
record (in this example, a record in the Departmt table) but does not uniquely
identify the current record (in the Employee table).
Building flexible search interfaces
If you want users to optionally enter multiple search criteria, you can wrap
conditional logic around the SQL AND clause to build a flexible search interface. To
test for multiple conditions, wrap additional
cfif
tags around additional AND
clauses.