Release 10.1A: OpenEdge Development:
Programming Interfaces

> ## Using inner joins

> The inner join is the default join type in a multi-table read or query. Use this type of join where you are only concerned with the data on the left side of the join for which there is related data on the right. For example, you might want to see only those customers whose purchase of a single item comes close to their credit limit.

> The query in `i-join1.p` performs three inner joins on the sports database to return this data. These three items correspond to the `/*1*/`, `/*2*/`, and `/*3*/` that label the joins in the `i-join1.p` example:

> 1. To each Customer, join all related Order records.

> 2. To each Order in the previous join, join each related Order–Line record whose total purchase is greater than two-thirds the customer’s credit limit.

> 3. To each Order–Line in the previous join, join the related Item record to get the item name.

> i-join1.p
>     DEFINE QUERY q1 FOR Customer FIELDS (Name Credit-Limit), Order FIELDS
>     (Order-Num), Order-Line FIELDS (Price Qty), Item FIELDS (Item-Name).
>     DEFINE BROWSE b1 QUERY q1
>         DISPLAY Customer.Name Customer.Credit-Limit Order.Order-num
>                 Item.Item-Name
>     WITH 10 DOWN.
>     OPEN QUERY q1 PRESELECT EACH Customer,
>         EACH Order OF Customer,
>         EACH Order-Line OF Order
>             WHERE (Order-Line.Price * Order-Line.Qty) >
>                   (.667 * Customer.Credit-Limit),
>         EACH Item OF Order-Line.

>     ENABLE b1 WITH SIZE 68 BY 10.
>     WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW.

> When executed, you get the output shown in Figure 1–5. Thus, the relation of Order to Customer and the selection criteria on Order–Line reduces the total number of query rows from thousands of possible rows to four.

> Figure 1–5: Inner join example

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095