Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

Note that we’re using the IN operator instead of the equality operator (=). We

do so because our subquery could return a list of values. For example, if we added

another department with the name “Product Engineering,” or accidentally added

another Engineering record to the Departments table, our subquery would return

two IDs. So, whenever we’re dealing with subqueries like this, we should use the

IN operator unless we’re absolutely certain that the subquery will return only one

record.

Querying Multiple Tables

When using queries that involve multiple tables, it’s useful to take a look at

the database diagram you created in Chapter 7 to see what columns exist in

each table, and to get an idea of the relationships between the tables.

Table Joins

An inner join allows you to read and combine data from two tables between

which a relationship is established. In Chapter 7, we created such a relationship

between the Employees table and the Departments table using a foreign key.

Let’s make use of this relationship now, to obtain a list of all employees in the

engineering department:

SELECT Employees.Name

FROM Departments

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

WHERE Departments.Department LIKE '%Engineering'

The first thing to notice here is that we qualify our column names by preceding

them with the name of the table to which they belong, and a period character

(.). We use Employees.Name rather than Name, and Departments.DepartmentID

instead of DepartmentID. We need to specify the name of the table whenever

the column name exists in more than one table (as is the case with DepartmentID);

in other cases (such as with Employees.Name), adding the name of the table is

optional.

As an analogy, imagine that you have two colleagues at work named John. John

Smith works in the same department as you, and his desk is just across the aisle.

John Thomas, on the other hand, works in a different department on a different

floor. When addressing a large group of colleagues, you would use John Smith’s

full name, otherwise people could become confused. However, it would quickly

become tiresome if you always used John Smith’s full name when dealing with

309

Table Joins