Limiting the records in a recordset

Use a WHERE clause to limit the number of records in the recordset. For example, you may want to include only those customers who earn more than $50,000 a year. Assume you have a column in your table called Earnings that tells you how much each customer earns. Your SELECT statement would read as follows:

SELECT YearBorn, DateLastPurchase FROM Customers 
WHERE Earnings > 50000

You specify one or more conditions in a WHERE clause to filter out records in the database. The following sections describe ways to filter records with the WHERE clause:

• Filtering records based on the equality of two values
• Filtering records based on the likeness of two values
• Filtering records based on a range of values
• Filtering records based on a combination of search conditions

Filtering records based on the equality of two values

You can filter records in a database based on the equality of a parameter’s value with a record column’s value.

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

• Check a record in the database table.
• If the value in the department column of the record is equal to the department name submitted by the user, then include that record in the search results recordset.
• Check the next record in the table.

You can express this logic with the following WHERE clause:

WHERE ColumnName = ParameterValue

ParameterValue is a SQL variable containing a search parameter. In a web application, the user typically supplies this parameter using an HTML form.

This database query could be expressed fully in SQL as follows:

SELECT FIRSTNAME, LASTNAME, DEPARTMENT, EMAIL
FROM EMPLOYEES
WHERE DEPARTMENT = 'varDept'

This SQL statement finds all the records in the employee table with a DEPARTMENT value equal to the value contained in the varDept variable. For example, if the user specifies Operations as the department name, the SQL statement might generate the following recordset:

Filtering records based on the likeness of two values

You can filter records in a database based on the likeness of a parameter’s value with a record column’s value.

Using likeness instead of equality gives users more flexibility when specifying the value of search parameters. For example, search words don’t need to be case sensitive. If the user enters ohio and the table column contains the value Ohio, the match is made.

Also, likeness lets you use wildcard characters so users can perform alphabetical and partial-word searches. For example, if the user enters m and the table column contains the values Morgan, Macy, and Michelson, then you can use a wildcard character in the SQL statement so that all three matches are made.

The standard wildcard character is the percentage sign (%):

...WHERE LastName LIKE 'Mc%'

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

• Check a record in the database table.
• If the value in the last name column of the record contains a value like the value 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 LIKE ParameterValue

ParameterValue is a SQL variable containing a search parameter. In a web application, the user typically supplies this parameter using an HTML form.

This database query could be expressed fully in SQL as follows:

SELECT FIRSTNAME, LASTNAME, DEPARTMENT, EMAIL
FROM EMPLOYEES
WHERE DEPARTMENT LIKE 'varLastName'

If you want to give users the ability to perform partial-word searches, combine the variable with a wildcard character. The SQL wildcard character to use in this case is the percentage sign (%). Here’s an example:

...WHERE LASTNAME LIKE 'varLastName%'

For example, if the user types s as the search parameter, all records with last names starting with the letter s are included in the recordset, as in the following example:

If the user specifies sm as the search parameter, then only those records with last names that start with the letters sm are included in the recordset:

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' 

If you want one condition to be true but not another, use the NOT operator as follows:

...WHERE DEPARTMENT LIKE 'varDept' AND NOT COUNTRY LIKE 'varCountry' 

You can use parentheses to group search conditions:

...WHERE (DEPARTMENT LIKE 'varDept' AND STARTDATE < #varStart#)
OR STARTDATE BETWEEN #varStartRange# AND #varEndRange#