Figure 7-7 shows the parent/child relationships in this query. Logically, it extends the join sequence of the previous example one more step, linking an order to its customer, the customer to their salesperson, and the salesperson to their office.

Using Barcode encoder for Software Control to generate, create ANSI/AIM Code 39 image in Software applications.

The vast majority of multitable queries are based on parent/child relationships, but SQL does not require that the matching columns be related as a foreign key and primary key. Any pair of columns from two tables can serve as matching columns, provided they have

Using Barcode generator for iPhone Control to generate, create UCC - 12 image in iPhone applications.

comparable data types (or data types that can be converted to compatible types). This example shows a query that uses a pair of dates as matching columns: Find all orders received on a day when a new salesperson was hired.

The results of this query come from pairs of rows in the ORDERS and SALESREPS tables where the ORDER_DATE happens to match the HIRE_DATE for the salesperson, as shown in Figure 7-8. Neither of these columns is a foreign key or a primary key, and the relationship between the pairs of rows is admittedly a strange one the only thing the matched orders and salespeople have in common is that they happen to have the same dates. However, SQL happily joins the tables anyway. Matching columns like the ones in this example generate a many-to-many relationship between the two tables. Many orders can share a single salesperson s hire date, and more than one salesperson may have been hired on the same date. For example, note that three different orders (112968, 112975, and 112979) were received on October 12, 2007, and two different salespeople (Larry Fitch and Mary Jones) were hired that same day. The three orders, each matched to both of the two salespeople, produce six rows of query results. This many-to-many relationship is different from the one-to-many relationship created by primary key/foreign key matching columns. The situation can be summarized as follows: Joins that match primary keys to foreign keys always create one-to-many, parent/ child relationships. Other joins may also generate one-to-many relationships if the matching column in at least one of the tables has unique values for all rows of the table. In general, joins on arbitrary matching columns generate many-to-many relationships. Note that these three different situations have nothing to do with how you write the SELECT statement that expresses the join. All three types of joins are written the same way by including a comparison test for the matching column pairs in the WHERE clause or in the ON clause. Nonetheless, it s useful to think about joins in this way to understand how to turn an English-language request into the correct SELECT statement.