Re: Curious SQL question

-CELKO- wrote:
> 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. Even for the small things, it is just good manners --
> look at all the assumptions we have to make
>
> CREATE TABLE Products
> (sku CHAR(9) NOT NULL PRIMARY KEY,
> product_name VARCHAR(20) NOT NULL,
> etc );
>
> CREATE TABLE Orders
> (customer_id INTEGER NOT NULL
> REFERENCES Customers (customer_id)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> sku CHAR(9) NOT NULL
> REFERENCES Products(sku)
> ON UPDATE CASCADE,
> order_qty INTEGER NOT NULL
> CHECK (order_qty > 0),
> PRIMARY KEY (customer_id, sku),
> ..);
>
> CREATE TABLE Products
> (sku CHAR(9) NOT NULL PRIMARY KEY,
> product_name VARCHAR(20) NOT NULL,
> etc );
>
> You left out this table, which must exist if there is a customer
> identifier
>
> CREATE TABLE Customers
> (customer_id INTEGER NOT NULL PRIMARY KEY,
> ..);
>

I disagree. Perhaps his is a data-mining database which does not contain any personal identifying information. In such a case, it would be quite sensible that from the perspective of the OP's database, CUSTOMER_ID is merely an attribute of an ORDER, not an identifier of a customer entity.

As has been pointed out already, the problem is still easily resolved by projecting customer_id and crssing that against products.

> >> How can I find out what products, each customer DIDN'T buy? <<
>
> SELECT C.customer_id, P2.sku
> FROM (Customers AS C
> CROSS JOIN
> Products AS P2)
> EXCEPT
> (SELECT O.customer_id, P1.sku
> FROM Orders AS O, Products AS P1
> WHERE O.sku = P1.sku) AS CP (customer_id, sku);
>
> The idea is to cross join to get all combinations, then sert difference
> out the actual purchases. You can also do this with [NOT] EXISTS()
> predicates.
Received on Thu Jan 04 2007 - 16:49:24 CET