Query improvement (avoiding code duplication, etc) [message #350288] |
Wed, 24 September 2008 10:31  |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
Hi.
With some helpful advice from these forums, I wrote a SQL query which appears to do what I want it to do. However, it's pretty bulky and difficult to maintain. It also duplicates the same code in two places, which I'd prefer to avoid. I'd appreciate any suggestions for how to do improve upon what I currently have.
I've written up some context for what I need the query to return. And, before you ask, I cannot change the table structure. The actual query is below, if you just want to skip to it.
====
Let's say I have an 'order' table containing some orders, and a 'special_orders' table which contains additional information for special orders. If an order is a special order, then exactly one entry exists in the special_orders table with the corresponding order_id; otherwise, no special_orders entry exists. This is the only way to tell if an order is a special order.
If more than one unfilled order is to be shipped to the same customer at the same address, I want to see it so that I can combine the two orders to save on shipping costs. However, 'fragile' orders can never be combined, and should therefore not be returned by this search.
In other words, if a record meets the following criteria, it should be returned:
1) The order is not a 'fragile' order. (An order is 'fragile' only if a special_order with a 'handling' column value of 'fragile' exists for that order.)
2) The order has not yet shipped (shipped_date is null).
3) Another record exists which meets 1) and 2) and also shares the same customer_id and shipping_address as this order.
SELECT A.customer, A.address, A.order_number
FROM (SELECT orders.*, special_orders.handling
FROM orders LEFT JOIN special_orders.handling
ON orders.order_id=special_orders.order_id
WHERE NOT special_orders.handling='fragile'
AND orders.shipped_date IS NULL) A,
(SELECT orders.*, special_orders.handling
FROM orders LEFT JOIN special_orders.handling
ON orders.order_id=special_orders.order_id
WHERE NOT special_orders.handling='fragile'
AND orders.shipped_date IS NULL) B
WHERE A.shipping_address = B.shipping_address
AND A.customer_id = B.customer_id
AND A.main_id <> B.main_id
ORDER BY A.customer, A.address
So, how can I make this better?
|
|
|
|
Re: Query improvement (avoiding code duplication, etc) [message #350342 is a reply to message #350328] |
Wed, 24 September 2008 15:27  |
aadwight
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
That looks like exactly what I need to do. Thanks.
Incorporating a WITH clause, the query looks like this. Can any further improvements be made toward efficiency, maintainability, or readability?
WITH (SELECT orders.*, special_orders.handling
FROM orders LEFT JOIN special_orders.handling
ON orders.order_id=special_orders.order_id
WHERE NOT special_orders.handling='fragile'
AND orders.shipped_date IS NULL) AS combinable_orders
SELECT A.customer, A.address, A.order_number
FROM combinable_orders A, combinable_orders B
WHERE A.shipping_address = B.shipping_address
AND A.customer_id = B.customer_id
AND A.main_id <> B.main_id
ORDER BY A.customer, A.address
|
|
|