Multiple outer joins
Posted on 2005-05-03
I'm trying to create a spreadsheet that displays purchase order details and corresponding invoice details where they exist. I'm using MS Excel 2003 and MS Query to access Sage Line 100 via ODBC.
Basically, there are two sets of master and detail tables: order_header and order_detail; and invoice_header and invoice_detail.
I can easily display all the order_detail and coresponding invoice_detail where it exists -- a simple outer join -- but there are several column I need from the header tables.
I've got this partially working, but as soon as I try to add values from the invoice_header table, only complete rows are selected -- the outer join functionality seems to be lost.
If we forget the actual columns I'm selecting for the moment, the SQL query looks something like this (actual tables and column names changed for brevity):
FROM ORDER_HEADER, ORDER_DETAIL, INVOICE_DETAIL
WHERE ORDER_HEADER.ORDER_ID = ORDER_DETAIL.ORDER_ID AND ORDER_DETAIL.ITEM_ID = INVOICE_DETAIL.ITEM_ID(+)
I now need to add INVOICE_HEADER into the query. For your information, each INVOICE_DETAIL row will have a matching entry in INVOICE_HEADER. Not all INVOICE_HEADER rows have matching INVOICE_DETAIL.
I know this isn't exactly clear, but if you need any other info, I'll do my best to explain.