Creating Table Joins

Many times, the data that you want to retrieve is maintained in multiple tables.

For example, in the database that you're working with:

Department information is maintained in the Departments table.

This includes department ID numbers.

Employee information is maintained in the Employees 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.

Usage example

For example, to return employee names, start dates, department names, and salaries for employees that work for the HR department, you would build a query that looks like this:

<CFQUERY NAME="GetEmployees" DATASOURCE="HRApp"> 
    SELECT Departments.Department.Name, 
    Employees.FirstName,
    Employees.LastName,
    Employees.StartDate,
    Employees.Salary
    FROM Departments, Employees
    WHERE Departments.Department_ID = Employees.Department_ID
    AND Departments.Department_Name = 'HR'
</CFQUERY>

Prefix each column in the SELECT statement to explicitly state which table the data should be retrieved from.
The Department_ID field is the primary key of the Deparments table and the Foreign Key of the Employees table.