multiple outter joins using sql92

[duckyboyz]

can some one show me an example of the syntax for
doing an outter join in sql92 using several tables?

 

[pcorreia]

Given the following tables (data types are from T-SQL):

tblCustomers
-----------------------------
customerID     int
customerName   varchar(100)
customerEmail  varchar(50)

tblOrders
-----------------------------
orderID        int
customerID     int
shipToAddress  varchar(100)
orderDate      datetime

tblOrderItems
-----------------------------
orderItemID    int
orderID        int
productID      int
quantity       int
priceGiven     money

A query to find all customers and their orders, if any, would look like this:

SELECT
   C.customerID,
   C.customerName,
   C.customerEmail,
   O.orderID,
   O.shipToAddress,
   O.orderDate,
   SUM(I.quantity) AS totalItems
FROM
   tblCustomers C
   LEFT OUTER JOIN tblOrders O on C.customerID = O.customerID
   LEFT OUTER JOIN tblOrderItems I on O.orderID = I.orderID
GROUP BY
   C.customerID,
   C.customerName,
   C.customerEmail,
   O.orderID,
   O.shipToAddress,
   O.orderDate

Using the outer join in this case assured that all customers will be shown, even if they have not placed any orders, and all orders will be shown, even if they contain no items. Using an inner join in either of these cases would have resulted in customers with no orders and orders with no items to be left out of the result set.