As demonstrated so far and in previous lessons, the main reason for creating queries is to isolate records. This is done using conditions and criteria. Joins enhance this capability because they allow you to consider records from different tables and include them in a common SQL statement.

In the joins we have created so far, we considered all records and let the database engine list them using only the rules of joins built-in the SQL. To make such a list more useful or restrictive, you can pose your own conditions that should be respected to isolate records like a funnel. As done in previous lessons, to include a criterion in a SELECT statement, you can create a WHERE clause.

To create a criterion in a query you create from the table view in the Microsoft SQL Server Management Studio or Microsoft Visual Studio, first select a column to display it in the Grid section. Just as reviewed in the previous lessons when creating a query, to specify a criterion, in the Criteria box corresponding to the column, type the condition using any of the operators we reviewed in previous lessons. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
       Sexes.SexID, Sexes.Sex
FROM   Persons LEFT OUTER JOIN
       Sexes ON Persons.SexID = Sexes.SexID
WHERE  Sexes.Sex = 'female'

This would produce:

1. Right-click an area of the Query Builder window and click Add Table
2. In the Add Table dialog box, double-click Conditions and click Close
3. Drag ConditionID from the Conditions table and drop it on ConditionID from the Properties table
4. Click the check boxes of the following columns PropertyType, City, State, Bedrooms, Bathrooms, YearBuilt, Condition, and MarketValue
5. Right-click the window and click Execute SQL
    
   
6. Click OK and click Finish
7. On the form, click the data grid view
8. In the Properties window, set its DataSource to None and set it again to bsProperties
9. On the form, double-click the Show combo box and implement the event as follows:
    

   private void cbxShow_SelectedIndexChanged(object s}er, EventArgs e) { if (cbxShow.SelectedIndex == 1) bsProperties.Filter = "PropertyType = 'Townhouse'"; else if (cbxShow.SelectedIndex == 2) bsProperties.Filter = "PropertyType = 'Condominium'"; else if (cbxShow.SelectedIndex == 3) bsProperties.Filter = "PropertyType = 'Single Family'"; else bsProperties.Filter = ""; }

10. Execute the application to see the result
     
11. Close the form and return to your programming environment