Databases Reference
In-Depth Information
Many queries require data from more than one table. As with QBE and relational algebra, it is necessary to
be able to join tables so you can find rows in two or more tables that have identical values in matching fields.
In SQL, this is accomplished by entering the appropriate conditions in the WHERE clause. (Appendix B
includes information about an alternative way of joining tables in SQL that uses the FROM clause.)
97
EXAMPLE 24
List the number and name of each customer together with the number, last name, and first name of the sales
rep who represents the customer. Order the records by customer number.
Because the numbers and names of customers are in the Customer table and the numbers and names of
sales reps are in the Rep table, you need to include both tables in your SQL query. To join the tables, you'll
construct the SQL command as follows:
1. In the SELECT clause, list all fields you want to display.
2. In the FROM clause, list all tables involved in the query.
3. In the WHERE clause, give the condition that will restrict the data to be retrieved to only those
rows from the two tables that match; that is, you'll restrict it to the rows that have common val-
ues in matching fields.
As in relational algebra, it is often necessary to qualify a field name to specify the particular field you are
referencing. To qualify a field name, precede the name of the field with the name of the table, followed by a
period. For example, the RepNum field in the Rep table is written as Rep.RepNum and the RepNum field in the
Customer table is written as Customer.RepNum. The query design appears in Figure 3-48.
Two tables
in query
Condition to join
the tables
Qualified field
names
FIGURE 3-48
SQL query to join tables
