Re: SQL Problem

1 - since there is no row in the notes table - there is no 'IS NULL' condition to test for - removing the OUTER is indeed what you need to do to restrict the result set to only those rows which have notes. 2 - since your join to orderdetailnotes relies on the line_no from ordernotes - when there is no row match to ordernotes - you will indeed not be able to get the detail notes. In other words change the join to orderedetailnotes to use the orderno from orderdetail and you should be fine. In fact that's really what you wanted in the first query as well. cheers j. Please respond to "Fred F." <mel.mullen@NOSPAM-mail.com> To: informix-list@rmy.emory.edu cc: (bcc: Jack Parker/Boston50/Epsilon) Subject: SQL Problem 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? -- Mel Mullen ( mel.mullen@mail.com )