Release 10.1A: OpenEdge Development:
Programming Interfaces

> ## Using left outer joins

> A left outer join is useful where you want to see all the data on the left side, whether or not there is related data on the right. For example, you might want to see the proportion of customers who are ordering close to their credit limit as against those who are not.

> The query in `i-join2.p` is identical to the one in `i-join1.p` (see the "Using inner joins" section) except that all the joins are left outer joins instead of inner joins. Thus, you see all customers, whether or not they order close to their credit limit. These three items correspond to the `/*1*/`,`/*2*/`, and `/*3*/` that label the joins in the `i-join2.p` example:

> 1. To each Customer, join all related Order records, or join a null Order record if the customer has no orders.

> 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, or join a null Order–Line record if all item purchases are less than or equal to two–thirds the customer’s credit limit. Also, join a null Order–Line record if the order is null (the customer has no orders).

> 3. To each Order–Line in the previous join, join the related Item record to get the item name, or join a null Item record if the Order–Line is null (no Order or selected purchase for that customer).

> i-join2.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 OUTER-JOIN OF Customer,
>         EACH Order-Line OUTER-JOIN OF Order
>             WHERE (Order-Line.Price * Order-Line.Qty) >
>                   (.667 * Customer.Credit-Limit),
>         EACH Item OUTER-JOIN 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–6. In this example, you see the same golf club order as in Figure 1–5 along with many other orders that do not meet the selection criteria and some customers who have no orders at all.

> Figure 1–6: Left outer join example

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