Outer join of two tables to a third table

Using the HCL Informix® extension syntax, the following query shows an outer join that is the result of an outer join of each of two tables to a third table. In this third type of outer join, join relationships are possible only between the dominant table and the subservient tables.

Figure 1. Query

SELECT c.customer_num, c.lname, o.order_num, 
     order_date, call_dtime
   FROM customer c, OUTER orders o, OUTER cust_calls x
   WHERE c.customer_num = o.customer_num
      AND c.customer_num = x.customer_num
   ORDER BY lname
   INTO TEMP service;

The query individually joins the subservient tables orders and cust_calls to the dominant customer table; it does not join the two subservient tables. An INTO TEMP clause selects the results into a temporary table for further manipulation or queries, as the result shows.

Figure 2. Query result

customer_num lname             order_num order_date call_dtime

         114 Albertson
         118 Baxter
         113 Beatty
         103 Currie
         115 Grant                  1010 06/17/1998
         ⋮
         117 Sipes                  1012 06/18/1998
         105 Vector
         121 Wallack                1018 07/10/1998 1998-07-10 14:05
         106 Watson                 1004 05/22/1998 1998-06-12 08:20
         106 Watson                 1014 06/25/1998 1998-06-12 08:20

If Figure 1 had tried to create a join condition between the two subservient tables o and x, as the following query shows, an error message would indicate the creation of a two-sided outer join.

Figure 3. Query

WHERE o.customer_num = x.customer_num