Re: SQL Problem
Hi,
It seems there is a mistake in your where clause !
For the first case it must be like :
SELECT order.order_no, orderdetail.line_no, ordernotes.text,
orderdetailnotes.text
FROM orders, orderdetails, OUTER ordernotes, OUTER orderdetailnotes
WHERE order.order_no = orderdetail.order_no AND
order.order_no = ordernotes.order_no AND
orderdetail.order_no = orderdetailnotes.order_no AND
orderdetail.line_no = orderdetailnotes.line_no
ORDER BY order.order_no, orderdetail.line_no
If you would like to get orders only have ordernotes then remove
OUTER from begining of the ordernotes.
( The other OUTER at the begining of the orderdetailnotes must remain )
Gokhan TERCAN
DBA
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?
}
} --
} Mel Mullen ( mel.mullen@mail.com )