Re: Curious SQL question

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 4 Jan 2007 10:29:44 -0800
Message-ID: <1167935383.980744.314600_at_51g2000cwl.googlegroups.com>

-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,
> ..);
>
> >> 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.
I basicaly agree CELKO.
But, if using Guto's original tables, following may be one answer. CREATE TABLE Guto.Products
(Product_ID INTEGER NOT NULL PRIMARY KEY ,Product_Name VARCHAR(10) NOT NULL
);

INSERT INTO Guto.Products

VALUES (1, 'shampoo')
     , (2, 'soap')
     , (3, 'bread')
     , (4, 'wine');

CREATE TABLE Guto.Orders
(Customer_ID VARCHAR(10) NOT NULL
,Product_ID INTEGER NOT NULL REFERENCES Guto.Products ,PRIMARY KEY (Customer_ID, Product_ID)
);

INSERT INTO Guto.Orders

VALUES ('john', 1)
     , ('john', 2)
     , ('mary', 2)
     , ('mary', 3);

>> How can I find out what products, each customer DIDN'T bought? <<
I used INNER JOIN with ON 0=0. Because my using DBMS doesn't support CROSS JOIN.

------------------------- Commands Entered -------------------------

SELECT Customer_ID, Product_Name
  FROM (SELECT DISTINCT

               Customer_ID
          FROM Guto.Orders) C
       INNER JOIN
       Guto.Products P
         ON 0=0
 WHERE NOT EXISTS
       (SELECT *
          FROM Guto.Orders O
         WHERE O.Product_ID  = P.Product_ID
           AND O.Customer_ID = C.Customer_ID)
 ORDER BY
       Customer_ID, Product_Name;
------------------------------------------------------------------------------

CUSTOMER_ID PRODUCT_NAME
----------- ------------

john        bread
john        wine
mary        shampoo
mary        wine

  4 record(s) selected.

Or

------------------------ Commands Entered ------------------------

SELECT Customer_ID, Product_Name
  FROM (SELECT C.Customer_ID, Product_Name

          FROM (SELECT DISTINCT
                       Customer_ID
                  FROM Guto.Orders) C
                       INNER JOIN
                  Guto.Products
                    ON 0=0
       ) CP
       EXCEPT
       SELECT O.Customer_ID, Product_Name
         FROM Guto.Orders   O
              INNER JOIN
              Guto.Products P
                ON O.Product_ID  = P.Product_ID
 ORDER BY
       Customer_ID, Product_Name;
------------------------------------------------------------------------------

CUSTOMER_ID PRODUCT_NAME
----------- ------------

john        bread
john        wine
mary        shampoo
mary        wine        

  4 record(s) selected. Received on Thu Jan 04 2007 - 19:29:44 CET