Hi all,

I have 4 tables (products, orders, order_details and stock).

Products (columns: id, name). This table is a list of all products
available.
Orders (columns: id, date). This table is a list of orders for a particular
date.
Order_details (columns: id, order_id, product_id). Subtable of orders. Lists
what products are in which order.
Stock (columns: id, product_id, qty). This table lists which products are
currently in stock. If the product is not in this table, then it is not in
stock.

Now, from the above I need to create 2 queries:

Query 1: list all distinct orders that have product a, b, or c in them. This
was quite easy.

Query 2. list all distinct orders that have all products in stock. That is,
I have 3 products (a, b, c) but only a and b are in stock. I have 2 orders
that have products a, b, c and, a, b listed. The query should return only
the second order because only products a and b are in stock, c is not.

Is it possible to define such a query? If yes, an ideas?

Thanks,
Ivan

Please post your table DDLs, sample data & expected results. For details,
refer to: www.aspfaq.com/5006

Hello, Ivan

Let's consider this DDL and sample data:

CREATE TABLE products (
    product_id int IDENTITY PRIMARY KEY,
    product_name varchar(50) NOT NULL UNIQUE
)

CREATE TABLE orders (
    order_id int IDENTITY PRIMARY KEY,
    order_date smalldatetime
)

CREATE TABLE order_details (
    order_detail_id int IDENTITY PRIMARY KEY,
    order_id int NOT NULL REFERENCES orders,
    product_id int NOT NULL REFERENCES products,
    UNIQUE (order_id, product_id)
)

CREATE TABLE stock (
    stock_id int IDENTITY PRIMARY KEY,
    product_id int NOT NULL REFERENCES products,
    qty numeric(18,3) NOT NULL
)

INSERT INTO products VALUES ('a')
INSERT INTO products VALUES ('b')
INSERT INTO products VALUES ('c')

INSERT INTO orders VALUES ('20060101')
INSERT INTO orders VALUES ('20060102')
INSERT INTO orders VALUES ('20060103')
INSERT INTO orders VALUES ('20060104')

INSERT INTO order_details VALUES (1,1)
INSERT INTO order_details VALUES (1,2)
INSERT INTO order_details VALUES (1,3)

INSERT INTO order_details VALUES (2,1)
INSERT INTO order_details VALUES (2,2)

INSERT INTO order_details VALUES (3,1)
INSERT INTO order_details VALUES (3,3)

INSERT INTO order_details VALUES (4,1)

INSERT INTO stock VALUES (1, 10)
INSERT INTO stock VALUES (2, 20)

The following queries provide almost the same results (but the first is
more clear and it also seems to be faster):

SELECT * FROM orders o WHERE NOT EXISTS (
    SELECT * FROM order_details d
    WHERE o.order_id=d.order_id
    AND d.product_id NOT IN (
        SELECT s.product_id FROM stock s
    )
)

SELECT * FROM orders o WHERE order_id IN (
    SELECT order_id FROM order_details d1
    INNER JOIN stock s ON d1.product_id=s.product_id
    GROUP BY order_id
    HAVING COUNT(*)=(
        SELECT COUNT(*) FROM order_details d2
        WHERE d1.order_id=d2.order_id
    )
)

There is a small difference in the results returned by the above
queries: if there is an order without any order details, the first
query returns it, but the second query doesn't.

This problem resembles the "relational division" problem, discussed by
Joe Celko in this article:
http://www.dbazine.com/ofinterest/oi-articles/celko1

Razvan

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. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code WHEN you do NOT let us
see it.

I also see that in complete violation of RDBMS, you put a magical,
universal "id" in your vague pseudo-code. That crap is record
numbers FROM sequential file systems AND has nothing to do a relational
key. There are some ISO-11179 violations. I hope that you know better
than to use IDENTITY for a key or anything else.

I also see you did no basic research for industry standards. What is
used to encode the products? EAN? UPC? what?   Let's do your job for
you:

CREATE TABLE Products
(upc CHAR(13)PRIMARY KEY, -- industry standards!!
product_name CHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Orders
(order_nbr INTEGER IDENTITY PRIMARY KEY
   CHECK (<< check digit logic >>),
order_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
..);

What was the reason for that redundant, magical, exposed physical
locator when you have a natural key??

CREATE TABLE OrderDetails
(order_id INTEGER NOT NULL REFERENCES Orders(order_nbr),
upc INTEGER NOT NULL REFERENCES Products(upc),
PRIMARY KEY (order_id, product_id),
...);

I would use Inventory rather than Stock because Stock is a sub-set of
Inventory whjcih can be shown in a VIEW, but okay, this is a minimal
exmaple.  A small business with nothing on order, on hold, damaged,
etc. might have stock be the same thing as inventory.
CREATE TABLE Stock
(upc CHAR(13) NOT NULL
       REFERENCES products (upc),
on_hand_qty INTEGER NOT NULL
   CHECK (on_hand_qty >= 0));

SELECT DISTINCT O.order_nbr, O.upc, P.product_name
  FROM OrderDetails AS O, Products AS P
WHERE O.upc IN (SELECT upc FROM Stock)
AND O.upc = P.upc;

This is called a relational division.  It was one of Dr. Codd's eight
basic operators when he defined RM.  You are dividing Orders by Stock.
Go over to www.DBAzine.com and look up one of my articles on it.  This
sounds too much like homework (i have seen it before in a DB class!),
so we will make your research it, instead of giving you the answer.

If you are not a cheating student, I apologize.  If you ARE a cheating
student, I hunt you down and try my best to get you thrown out of the
university.

Hierarchically arranged attributes...mission impossible?
problem: select Max(date) which is less than actual Max(date) ?
Parameterize table name without constructing dynamic query?
use trigger
An easy query: how to retrieve TOP n values of each <other field here>