Re: Curious SQL question

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 4 Jan 2007 06:46:39 -0800
Message-ID: <1167921999.037748.259210_at_q40g2000cwq.googlegroups.com>

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, ..);

>> 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 - 15:46:39 CET