Databases Reference
In-Depth Information
The query results appear in Figure 3-53.
100
FIGURE 3-53
Query results
The query shown in Figure 3-52 is more complex than many of the previous ones. You might think that SQL
is not such an easy language to use after all. If you take it one step at a time, however, you will find that the query
in Example 26 isn't all that difficult. To construct a detailed query in a step-by-step fashion, do the following:
1. List in the SELECT clause all the columns you want to display. If the name of a column appears in
more than one table, precede the column name with the table name (that is, qualify the column name).
2. List in the FROM clause all the tables involved in the query. Usually you include the tables that
contain the columns listed in the SELECT clause. Occasionally, however, there might be a table
that does not contain any columns used in the SELECT clause but that does contain columns
used in the WHERE clause. In this case, you must also list the table. For example, if you do not need
to list a customer number or name but you do need to list the sales rep name, you wouldn't
include any columns from the Customer table in the SELECT clause. The Customer table is still
required, however, because you must include columns from it in the WHERE clause.
3. Take one pair of related tables at a time and indicate in the WHERE clause the condition that relates
the tables. Join these conditions with the AND operator. When there are other conditions, include
them in the WHERE clause and connect them to the other conditions with the AND operator.
Recall from Chapter 2 that the union of two tables is a table containing all rows that are in the first table, the
second table, or both tables. The two tables involved in a union
must
have the same structure, or be union
compatible; in other words, they must have the same number of fields and their corresponding fields must have
the same data types. If, for example, the first field in one table contains customer numbers, the first field in
the other table also must contain customer numbers.
EXAMPLE 27
List the number and name of all customers that are represented by sales rep 35 or that currently have orders
on file or both.
Because the two criteria are so different, you cannot use a simple OR criterion. Instead, you can create
a table containing the number and name of all customers that are represented by sales rep 35 by selecting cus-
tomer numbers and names from the Customer table in which the sales rep number is 35. Then you can cre-
ate another table containing the number and name of every customer that currently has orders on file by
joining the Customer and Orders tables. The two tables created by this process have the same structure—fields
