If you want to see all customers regardless of whether they have an order, use a LEFT OUTER JOIN in a FROM clause. The keyword LEFT specifies that the table on the left (CUSTOMER in the example) is to be preserved, that is to say, all of the rows in the CUSTOMER table are to survive the join operation. The word OUTER is optional, that is, LEFT JOIN is equivalent to LEFT OUTER JOIN in the syntax. In the following outer join example, a row is returned for each CUSTOMER even if there is no matching ORDERS row.
SELECT T1.NAME, VALUE (SUM(T2.AMOUNT), 0)
FROM CUSTOMER T1 LEFT OUTER JOIN ORDERS T2
ON T1.CUSTNO = T2.CUSTNO
GROUP BY T1.NAME;
Using an outer join to see all customers is simpler than the alternate method (shown following) of coding a nested loop in your host application.
FOR EACH CUSTOMER
SET TOTAL = 0
FOR EACH ORDERS
WHERE ORDERS.CUSTNO = CUSTOMER.CUSTNO
SET TOTAL = TOTAL + ORDERS.AMOUNT
END FOR
PRINT CUSTOMER.NAME, TOTAL
END FOR
|
Copyright © 2014 CA.
All rights reserved.
|
|