HP 3000 Manuals HP 3000 Manuals
Joining Multiple Tables
A join is a select command that retrieves data from more than one table
at a time. This kind of query uses a column common to each pair of
tables to properly align the data. A join condition, which is a common
column in both tables, must be specified in the WHERE clause.
Suppose you want to know the PartNumber, VendorNumber, and VendorName for
every vendor who supplies each part. No single table contains all the
information you need. The SupplyPrice table has PartNumbers and
VendorNumbers, but no VendorNames. The Vendors table has VendorNumbers
and VendorNames, but no PartNumbers. Both tables also have additional
information which is of no interest to you.
To join the two tables, do the following:
1. In the select list, specify the needed column names.
PartNumber and VendorName are found in different tables, so there
is no ambiguity about where they come from. However, the
VendorNumber column is found in both tables. Therefore, you must
fully qualify the common column name with the table name so that
ALLBASE/SQL knows from which table to take the column. Fully
qualify the column name with the table name by specifying
"PurchDB.SupplyPrice.VendorNumber."
2. In the FROM clause, specify the tables being used for the join.
3. In the WHERE clause, specify the join condition. In the following
example, the VendorNumbers in each table must be equal for the
rows to be joined. In special cases, it is possible to use some
other operator such as > or <.
In the ORDER BY clause, you might want to sort the query result by
PartNumber.
The example below illustrates the above scenario. Note that the column
names are all fully qualified.
__________________________________________________________________________________
| |
| isql=> SELECT PurchDB.SupplyPrice.PartNumber. |
| > PurchDB.SupplyPrice.VendorNumber, |
| > PurchDB.Vendors.VendorName |
| > FROM PurchDB.SupplyPrice, |
| > PurchDB.Vendors |
| > WHERE PurchDB.SupplyPrice.VendorNumber = |
| > PurchDB.Vendors.VendorNumber |
| > ORDER BY PartNumber; |
| |
| SELECT PurchDB.SupplyPrice.PartNumber, |
| PurchDB.SupplyPrice.VendorNumber, Pur... |
| ----------------+------------+------------------------------ |
| PARTNUMBER |VENDORNUMBER|VENDORNAME |
| ----------------+------------+------------------------------ |
| 1123-P-01 | 9002|Dove Computers |
| 1123-P-01 | 9008|Jujitsu Microelectronics |
| 1123-P-01 | 9009|Latin Technology |
| 1123-P-01 | 9007|Eve Computers |
| 1123-P-01 | 9003|Space Management Systems |
| 1123-P-01 | 9012|Seminational Co. |
| 1133-P-01 | 9002|Dove Computers |
| 1133-P-01 | 9007|Eve Computers |
| 1133-P-01 | 9009|Latin Technology |
| 1133-P-01 | 9003|Space Management Systems |
| 1143-P-01 | 9007|Eve Computers |
| 1143-P-01 | 9008|Jujitsu Microelectronics |
| 1143-P-01 | 9009|Latin Technology |
| 1143-P-01 | 9004|Coupled Systems |
| 1153-P-01 | 9007|Eve Computers |
| 1153-P-01 | 9002|Dove Computers |
| ---------------------------------------------------------------------------|
| First 16 rows have been selected. |
| U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] |
| <n>, or e[nd] > end |
__________________________________________________________________________________