SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Sunday, September 10, 2006

Query Problem

SQL Apprentice Question
I have an orders table and an order_items table. Simply, they look like

ID | Status
0 | New
1 | InProgress
2 | InProgress

ID | Ord_ID | Supplier | Status
0 | 0 | Fred | New
1 | 1 | Fred | New
2 | 1 | Fred | Complete
3 | 2 | Fred | New
4 | 2 | Joe | Complete

When Joe wants to view his 'Complete' Orders, he should see order 2,
because all his items for order 2 are complete (even though its
orderstatus is inprogress)

When Fred wants to view his new orders, he should see order 0 and 2
(because all his items for 2 are new), and order 1 should be seen as

How can i write a query which given a supplier and status (either new,
inprogress or complete) will return all the relevant orders?


Celko Answers
Please post DDL, so that people do not have to guess what the keys,

constraints, Declarative Referential Integrity, data types, etc. in

your schema are.

Look at that Orders table -- vague "id" that might or might not be a
key (you are not using IDENTITY for order numbers, are you!!??); vague
"status" attribute (what kind of status??). Cann't this be done in a
view off of the OrderItems table instead of mimicing a file? Look up
how to name a data element.

Here is my guess and corrections to your "pseudo-code" non-table

CHECK (<< validation rule here>>),
CHECK (item_nbr > 0),
supplier_name CHAR(10) NOT NULL
REFERENCES Suppliers (supplier_name)
item_status CHAR(1) DEFAULT 'N' NOT NULL
CHECK (item_status IN ('N', 'C'), -- new, completed
PRIMARY KEY (order_nbr, item_nbr));

Notice the use of a relational key, instead of mimicing a tape file
record number? The use of IDENTITY for items in a bill of materials or
order problem screw up things. Use an item number within the order

INSERT INTO Order_Items VALUES (0, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (1, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (1, 2, 'Fred', 'C');
INSERT INTO Order_Items VALUES (2, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (2, 2, 'Joe', 'C');

That is, in Order #2, Fred supplied item #1 and Joe supplied item #2.
Lot easier to track things with a proper design. Now throw out your
redundant table:

CREATE VIEW OrderStatus (order_nbr, supplier, order_status)
SELECT order_nbr, supplier,
CASE WHEN MIN(item_status) = 'New'
THEN 'New'
WHEN MAX(item_status) = 'Complete'
THEN 'Complete'
ELSE 'In Progress' END;
FROM Order_Items
GROUP BY order_nbr, supplier;

The VIEW is always current and you do not have to keep writing to disk
to mimic a physical file.

No comments: