SQL Data Joins With ADO.NET

Joins and Data Analysis

Introduction

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 sections about queries, to include a criterion in a SELECT statement, you can create a WHERE clause.

Using Criteria

To create a criterion in a query you create from the SQL Server Enterprise Manager, 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:

After specifying the criteria, the database engine would automatically include it in the SQL statement.

Practical Learning: Using Criteria With Joins

1. Display the form. Under the form, right-click sqlDataAdapter1 and click Configure Data Adapter...
2. In the first page of the wizard, click Next
3. In the second page of the wizard, accept the Server.CarInventory2.dbo option in the combo box and click Next
4. In the third page of the wizard, accept the Use SQL Statement option and click Next
5. In the fourth page of the wizard, delete the SQL statement and click Query Builder
6. In the Add Table dialog box, double-click CarsCategories and Cars then click Close
7. In the CarsCategories section, click the check boxes on the left of CarCatetoryID and CarCatetory
8. In the Cars list, click the check boxes on the left of CarID, TagNumber, Make, Model, CarYear, HasK7Player, HasCDPlayer, and HasDVDPlayer
9. In the CarsCategories section, click the check boxes on the left of DailyRate, WeeklyRate, MonthlyRate, and WeekendRate
10. In the Cars list, click the check boxes on the left of CarCategoryID and Available
11. In the Grid section, change the Alias of the second CarCategoryID to Category
     
    
12. Click OK and then click Finish
13. On the main menu, click Data -> Generate Dataset...
14. In the Generate Dataset dialog box, accept the Existing radio button and click OK
15. Execute the application to see the results
16. Close the form