HP 3000 Manuals HP 3000 Manuals
E : View Table: Joining Three Tables You just defined two "layers" of view table (one of which builds on the other) to arrive at a table that displays sales dollars for each order. You can produce the same result by defining one view table that joins three tables. Call this table JOIN-3.
NOTE Since you have defined several view tables, the rest of this lesson asks you to apply what you've learned rather than stepping you through every keystroke.
If you had defined this one table only (instead of the two you just defined), the data dictionary would contain the following. Compare this configuration with that shown in Figure 5-5:Figure 5-7. View Table JOIN-3 Joins Three Tables _________________________________________________________ | | | References in this Learning manual: | | Lesson 2:G, Configuring a View Table | | Appendix A, Schema for SAMPL1 Database | | Appendix B, Schema for SAMPL2 Database | | | | References in Information Access Server: Database | | Administration: | | Chapter 5, Configuring Tables: | | Adding a View Table Definition | | Appendix B, Constructing View Tables | | Appendix C, View Table Syntax | _________________________________________________________ The definition of view table JOIN-3 uses the following items which, in the previous two exercises, were distributed between two view tables: ---------------------------------------------------------------------------------------------- | | | | | Database | Table | Items | | | | | ---------------------------------------------------------------------------------------------- | | | | | SAMPL1 | SALES-STAFF | | | | (IMAGE table) | EMP-NBR | | | | OFFICE | | | | | ---------------------------------------------------------------------------------------------- | | | | | SAMPL1 | ORDER-DETAIL | | | | (IMAGE table) | EMP-NBR | | | | PRODUCT-NBR | | | | ORDER-QTY * | | | | ORDER-DATE ** | | | | | ---------------------------------------------------------------------------------------------- | | | | | SAMPL2 | PRODUCTS | | | | | NUMBER | | | | PRODUCT | | | (View table) | PRICE * | | | | | ---------------------------------------------------------------------------------------------- * Don't list these as separate items. Use them in the expression that defines the new item EXTPRICE. ** Use this item in the Where Clause, not in the Item Clause. Which Items? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | E-1. In the first group of Add View Table | | | screens, name this table JOIN-3 and type a | | | description (optional). | | | | | --------------------------------------------------------------------------------------------- | | | | E-2. Use the items listed on the previous | | | page (except for ORDER-DATE) to fill in the | | | Item Clause. (An example is shown on the | | | next page.) | | | | | | For clarity, qualify by table name all | | | items common to any two of the three source | | | tables. For example, EMP-NBR, which occurs | | | in the first two tables listed, can be | | | entered either as SALES-STAFF.EMP-NBR or as | | | ORDER-DETAIL.EMP-NBR. (It does not matter | | | which, since the value in this item will be | | | matched for the JOIN operation.) | | | | | | Because NUMBER contains the same data as | | | PRODUCT-NBR (having merely been renamed), | | | you need only include one of them as an | | | item. The name you specify will appear as | | | the column head in the JOIN-3 view table. | | | Both names will be referenced in the JOIN | | | operation. | | | | | | Include the items ORDER-QTY and PRICE not | | | as individual items but as part of the | | | EXTPRICE item calculation (as in Lesson | | | 5:D, Step D-1). | | | | | | Remember to separate items with commas. | | | | | | (The SUB function and CASE statement, which | | | you used previously to include a full | | | product type description, are not included | | | in this table. This is because they | | | require a two-step table definition | | | process: one table to define the prefix as | | | an item, and another table to list the CASE | | | substitutions for that new item.) | | | | | --------------------------------------------------------------------------------------------- ________________________________________________________________________________________ | | | | | Using Clause (separated by commas) | | SALES-STAFF.EMP-NBR, | | OFFICE, | | NUMBER, | | PRODUCT, | | EXTPRICE = ORDER-QTY * PRICE | | | | | | | | | | | | | | | | | | | | | | | ________________________________________________________________________________________ --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | E-3. Display the next group of fields. | The Using, Where, and Sort Clause fields | | | appear on your screen. | | | | --------------------------------------------------------------------------------------------- Which Records? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | E-4. In the Using Clause, type the text | | | shown on the next page. | | | | | | Notice that the product number item on | | | which the data will be joined is specified | | | first as PRODUCT-NBR and then as NUMBER. | | | This is because ORDER-DETAIL is an IMAGE | | | table containing item names from the | | | dataset, while PRODUCTS is a view table | | | that renamed the PRODUCT-NBR item to | | | NUMBER. Since the data in these two items | | | is the same, the JOIN operation will find | | | matching values. | | | | | | Compare this syntax with the Using Clause | | | entries for the previous two view tables, | | | ORDERS and DOLLARS (defined in Lesson 5:C | | | and 5:D). | | | | | --------------------------------------------------------------------------------------------- _______________________________________________________________________________________ | | | | | Using Clause (separated by spaces) | | (SALES-STAFF <EMP-NBR> JOIN ORDER-DETAIL <EMP-NBR>) <PRODUCT-NBR> | | JOIN PRODUCTS <NUMBER> | | | | | | | _______________________________________________________________________________________ --------------------------------------------------------------------------------------------- | | | | | The Administrator Utility will carry out | | | the JOIN instructions within the | | | parentheses, then apply that result to a | | | second JOIN. | | | | | | First, items that derive from IMAGE tables | | | SALES-STAFF and ORDER-DETAIL will be | | | joined, based on matching EMP-NBR values. | | | | | | The resulting "orders by employee" | | | information will be joined with product and | | | pricing information from the view table | | | PRODUCTS, based on matching | | | PRODUCT-NBR/NUMBER values. | | | | | | Each record will include columns for the | | | employee number, office, product number, | | | product description, and extended price | | | (computed from order quantity and price | | | values). Because you specified JOIN | | | operations, only records which match on | | | SALES-REP and then on NUMBER will be | | | included in the view table. | | | | --------------------------------------------------------------------------------------------- | | | | E-5. In the Where Clause field, type: | Records will be selected with order dates | | | of January 1, 1990 or later, as in the view | | ORDER-DATE >= "900101" | table DOLLARS. | | | | | (Since the date item in this table is | | | derived from IMAGE table ORDER-DETAIL, you | | | must specify ORDER-DATE. By contrast, the | | | view table DOLLARS, defined in Lesson 5:D, | | | specified the renamed item DATE from the | | | ORDERS view table. The data retrieved will | | | be the same.) | | | | | --------------------------------------------------------------------------------------------- Table 5-0. (cont.) --------------------------------------------------------------------------------------------- | E-6. In the Sort Clause, type: | This table will sort selected records | | | alphabetically by office, with the highest | | OFFICE, EXTPRICE D | extended price listed first within each | | | office's group of records. | | | | --------------------------------------------------------------------------------------------- | | | | E-7. Configure the view table. | When this view table is requested by a PC | | | user, the view table constructed will be | | | virtually the same as the view table | | | DOLLARS. The only difference is that | | | product type will not have its own column. | | | | --------------------------------------------------------------------------------------------- | | | | E-8. Choose: | The Add Table Menu appears. From here you | | | can proceed to redefining a compound data | | Cancel f8 | item in a view table. | | Add | | | | | --------------------------------------------------------------------------------------------- What This Table Will Look Like An Access PC query that chooses columns EMP-NBR, NUMBER, PRODUCT, and EXTPRICE in view table JOIN-3 will produce this display:Figure 5-8. PC Display of View Table JOIN-3 Summary In this exercise, you learned how to: * Use the JOIN operation to combine records from three tables by nesting one JOIN command within another. * Combine data from two databases. * Match values of identical data items with different names.
![[FFN73]](../../pic3k/M012040/FFN73.gif)
![[FFN77]](../../pic3k/M012040/FFN77.gif)