Databases and Dreamweaver MX 2004, concluded - Filtering by Multiple Parameters
(Page 5 of 5 )
Highlight the Employee table from the Database Items tree and click SELECT to select all columns for this table. Test the query, and you will see all the employee records returned. Now change the query to select only employees who are on contract. Highlight the Contract column in the Employee tree, click on WHERE, and change theWHERE clause to say:
WHERE Employee.Contract = 'Yes'
Because the Contract column is a Text data type, you need to enclose its value in single quotation marks. Test the query, and you will see the seven employees who matched our search criteria.
Now let’s say you want to see only employees who are on contract and make more than $50,000 salary. Highlight Salary within the Employee tree and click the WHERE button. Now you must manually type a condition for Salary into the SQL text field to select for the condition where Salary is greater than 50,000. Add >50000 to the end of theANDclause line. Your final SQL code should look like this:
SELECT*
FROM Employee
WHERE Employee.Contract = 'Yes'
AND Employee.Salary > 50000
Click on the Test button and view the results. Only four employees match our current criteria, as shown in Figure 2-17.
Figure 2-17. The search results for contract employees
who earn more than $50,000
Creating a Simple Join
You can also select data from multiple tables at once. The data in the separate tables is linked by using primary and foreign keys.
In the following example, we join the Departmt and Employee tables and display the name of each employee, and each employee’s department name and location. Because the Employee table has only a Dept_ID column, you must join it with the Departmt table to get the values for Dept_Name and Location.
Let’s get started. Again clear the SQL code from the text field. Now expand both the Departmt and Employee tables and select the following columns (the order is unimportant): Departmt.Dept_ID, Departmt.Dept_Name, Departmt.Location, Employee.Emp_ID, Employee.FirstName, and Employee.LastName.
Your query should now look similar to the following:
SELECT Departmt.Dept_ID, Departmt.Dept_Name, Departmt.Location,
Employee.Emp_ID, Employee.FirstName, Employee.LastName
FROM Departmt, Employee
If you test this query, you will see that something has definitely gone wrong! (See Figure 2-18.)
Figure 2-18. Incorrect joining returns all possible
employee-department combinations.
For every employee, it has returned every employee and department combination possible! So, even though only 19 employees (and four departments) exist, it has returned 76 (19*4) records. The problem is that SQL still doesn’t know how to join these two tables. You must explicitly specify which columns you want to join these two tables on by using theWHERE statement.
Highlight the Employee.Dept_ID column in the Database Items tree. Click on WHERE, then update theWHEREstatement so that your final code looks like this:
SELECT Departmt.Dept_ID, Departmt.Dept_Name, Departmt.Location,
Employee.Emp_ID, Employee.FirstName, Employee.LastName
FROM Departmt, Employee
WHERE Employee.Dept_ID = Departmt.Dept_ID
Press the Test button, and you’ll see that you now have the correct results, and that each record returned has the employee’s first and last name, department, and location, as shown in Figure 2-19. The preceding snippet works because theWHERE clause joins the two tables via the common Dept_ID column.
Figure 2-19. Correct joining returns all employees with
their department name and location.
We mentioned earlier that you don’t need to prefix the name of the table onto each column when you are working with only one query. However, if you don’t prefix columns when dealing with multiple tables, SQL throws an error, stating that a specified field could refer to more than one table in theFROMclause of the query. For this reason, it is important to prefix the table name for any column that may appear in more than one table. In this case, it is the Dept_ID column that appears in both the Departmt and Employee tables. SQL allows you to assign aliases to table names, which helps reduce the amount of typing necessary if you are hand-coding SQL. You could rewrite the last query to use table aliases, as seen here:
SELECT D.Dept_ID, D.Dept_Name, D.Location,
E.Emp_ID, E.FirstName, E.LastName
FROM Departmt D, Employee E
WHERE E.Dept_ID = D.Dept_ID
We assign the table’s alias after the table name in theFROMline. Notice that the table names have been replaced by their aliases in all the SQL statements. It is important to note that if you create aliases for a table, you must always use that alias and not switch back and forth between aliases and table names; otherwise, errors will be thrown when executing queries.
The LIKEOperator
A useful operator in SQL is theLIKEoperator, which allows you to search columns for the occurrence of a character or string. For example, you can search for all employees whose last names start with the letter A. You can easily achieve this by modifying the preceding query, highlighting the Employee.LastName in the Database Items tree, and clicking on the WHERE button. Dreamweaver will appendAND Employee.LastNameto the query.
Now you need to addLIKE 'A%'to the query. You should also change theEmployeeprefix to the aliasE, or SQL will throw an error. Your final query should look something like this:
SELECT D.Dept_ID, D.Dept_Name, D.Location,
E.Emp_ID, E.FirstName, E.LastName
FROM Departmt D, Employee E
WHERE E.Dept_ID = D.Dept_ID
AND E.LastName LIKE 'A%'
If you click the Test button, you will see the three records that match the search criteria. The SQL statement now basically says “Select all records where employee’s last name begins with the letter ‘A’ and return their Dept_ID, Dept_Name, Location, Emp_ID, FirstName, and LastName.” The%character is a special wildcard character in SQL;A%, means “A, then anything after it.” Multiple wildcards can be used; a statement such asLIKE '%A%'will return all records that have an “A” anywhere in the column.
Summary
This has been a fairly quick introduction to databases and SQL. You should now be able to:
- Create an Access data source by using the ColdFusion Administrator
- Create some basic SQLSELECTstatements using the simple and advanced Recordset interface in Dreamweaver
- Perform a simple join
You should also be a little more familiar with the Microsoft Access interface, know how to browse tables using Design View and view the data, and have a better understanding of the different data types used in Access.
We revisit databases later in Chapter 6, where we look at creating and utilizing Recordsets with Dreamweaver in more detail; creating someINSERT,UPDATE, andDELETEstatements by writing our own SQL from scratch; creating queries with CFML; and more.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter two of the book ColdFusion Web Development with Dreamweaver MX 2004, written by Jen and Peter de Haan et al. (Apress, 2004; ISBN: 1590592379). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 3 
