Macromedia dreamweaver 8-using dreamweaver User Manual

1002 Appendix B:  SQL Primer

Filtering records based on a range of values

You can filter records in a database based on whether a record column’s value falls within the 
range of two parameter values. 

Suppose you decide to let users search the database by a date range. The following logic is 
required to build the search results recordset:

■

Check a record in the database table.

■

If the value in the date column of the record falls between the two date values submitted 
by the user, then include that record in the results recordset.

■

Check the next record in the table.

You can express this logic with the following 

WHERE

 clause:

WHERE ColumnName BETWEEN ParameterValue1 AND ParameterValue2

ParameterValue1

 and 

ParameterValue2

 are SQL variables containing search parameters. In 

a web application, the user typically supplies these parameters using an HTML form. 

Here’s how this type of database query can be expressed in SQL:

SELECT FIRSTNAME, LASTNAME, DEPARTMENT, STARTDATE
FROM EMPLOYEES
WHERE STARTDATE BETWEEN #varStartRange# AND #varEndRange#

For example, if the user enters 7/1/99 and 12/31/99 as the range parameters, all employees 
starting in the second half of 1999 are included in the recordset, as in the following example: 

Filtering records based on a combination of search 
conditions

This section describe how to include records in the search results recordset based on a 
combination of search conditions. You combine search conditions in SQL using the 

AND

, 

OR

, 

and 

NOT

 logical operators.

If you want all the conditions to be true for a record to be included in the recordset, use the 

AND

 operator as follows:

...WHERE LASTNAME LIKE 'varLastName' AND DEPARTMENT LIKE 'varDept' 

If you want any one of the conditions to be true for a record to be included in the recordset, 
use the 

OR

 operator as follows:

...WHERE LASTNAME LIKE 'varLastName' OR DEPARTMENT LIKE 'varDept'