Re: SQL Problem

Fred F. wrote: > Sorry if this is a bit long-winded but... > I have 4 tables: orders, orderdetails, ordernotes, and orderdetailnotes. > Every order will have at least one orderdetail and the respective notes > tables have an optional 1-to-1 relationship with their siblings (i.e. an > order record might have a notes record and each detail record might have a > notes record). The following query (as part of a report) works fine: > SELECT order.order_no, orderdetail.line_no, ordernotes.text,> orderdetailnotes.text > FROM orders, orderdetails, OUTER ordernottes, OUTER orderdetailnotes > WHERE order.order_no = orderdetail.order_no AND > order.order_no = ordernotes.order_no AND > ordernotes.order_no = orderdetailnotes.order_no AND > orderdetail.line_no = orderdetailnotes.line_no > ORDER BY order.order_no, orderdetail.line_no > > This returns all orders with their related order details plus any notes > where present. NB: If there are no ordernotes for a given order then I don't > want any orderdetailnotes returned either. > But now I want to alter the same query to return *only* those orders that > have ordernotes, so I try adding the line to the where clause: > AND NOT ordernotes.order_no IS NULL > This doesn't work. It returns the exact same result set as if I hadn't added > it. I then try removing the OUTER from the ordernotes table and this does > indeed restrict the orders to only those with notes... but now any > orderdetailnotes are missing. > Am I going about this the totally wrong way or am I missing something > simple? You need to prioritize the OUTER JOINs so that the OUTER clause on orderdetailnotes depends on orderdetails not on ordernotes as is implied by the table ordering. Either: 1) Reorder the tables in the FROM clause so that OUTER orderdetailnotes immediately succeeds orderdetail: FROM order, ordernotes, orderdetail, OUTER orderdetailnotes 2) Parenthesis the FROM clause so that the OUTER orderdetailnotes depends on the result of the join of the other three tables: FROM (order, orderdetail, ordernotes), OUTER orderdetailnotes Either should work for your particular query, however, there are other queries for which these two options will return different results. Art S. Kagel