Re: Curious SQL question
Date: 5 Jan 2007 09:09:55 -0800
Message-ID: <1168016995.682546.108760_at_11g2000cwr.googlegroups.com>
Guto wrote:
> WOW!
> Thanks for so many posts!
> Facing the doubts about my question, heres a "picture" wich describes
> it best than
> my poor english ;o)
>
> --here's the first table:
>
> create table products(
> product_id integer,
> product_name char(20));
>
> --then the second
> create table orders(
> customer_id integer,
> product_id integer);
>
> --The products to be boughted:
> insert into products values ( 1 , "PIZZA");
> insert into products values ( 2 , "BLACK BEER");
> insert into products values ( 3 , "WHITE BEER");
> insert into products values ( 4 , "ANOTHER BEER");
> insert into products values ( 5 , "ICE COLDED BEER");
>
> --the customers who bought something:
> insert into orders values ( 1,1);
> insert into orders values ( 1,2);
> insert into orders values ( 1,3);
> insert into orders values ( 1,4);
>
> insert into orders values ( 2,1);
> insert into orders values ( 2,2);
>
> insert into orders values ( 3,1);
> insert into orders values ( 3,3);
> insert into orders values ( 3,4);
>
> insert into orders values ( 4,5);
>
> --I want this list:
>
> --The customer 1 didn't bought the product 5
>
> --The customer 2 didn't bought the product 3
> --The customer 2 didn't bought the product 4
> --The customer 2 didn't bought the product 5
>
> --The customer 3 didn't bought the product 2
> --The customer 3 didn't bought the product 5
>
> --The customer 4 didn't bought the product 1
> --The customer 4 didn't bought the product 2
> --The customer 4 didn't bought the product 3
> --The customer 4 didn't bought the product 4
>
> --And as have tried this ( at least first obvious attempt didn't
> works)
> Select customer_id, product_id from orders
> where product_id not in ( select product_id from products )
>
> I hope i was a little bit clear this time!
> Thanks Indeed!
>
> Regards,
> Guto.
>
Is this meet your requirement?
------------------------- Commands Entered -------------------------SELECT * FROM GutoSupplied.products;
PRODUCT_ID PRODUCT_NAME
----------- --------------------
1 PIZZA
2 BLACK BEER
3 WHITE BEER
4 ANOTHER BEER
5 ICE COLDED BEER
5 record(s) selected.
- Commands Entered -------------------------
SELECT * FROM GutoSupplied.orders;
CUSTOMER_ID PRODUCT_ID
----------- -----------
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 3
3 4
4 5
10 record(s) selected.
My using DBMS(DB2) is not support CROSS JOIN. Then I used INNER JOIN with ON 0=0.
------------------------- Commands Entered -------------------------SELECT Customer_ID, Product_ID
FROM (SELECT DISTINCT
Customer_ID
FROM GutoSupplied.Orders) C
INNER JOIN
GutoSupplied.Products P
ON 0=0
WHERE NOT EXISTS
(SELECT *
FROM GutoSupplied.Orders O
WHERE O.Product_ID = P.Product_ID
AND O.Customer_ID = C.Customer_ID)
ORDER BY
Customer_ID, Product_ID;
------------------------------------------------------------------------------
CUSTOMER_ID PRODUCT_ID
----------- -----------
1 5
2 3
2 4
2 5
3 2
3 5
4 1
4 2
4 3
4 4
10 record(s) selected.
Or
------------------------- Commands Entered -------------------------SELECT Customer_ID, Product_ID
FROM (SELECT C.Customer_ID, P.Product_ID
FROM (SELECT DISTINCT
Customer_ID
FROM GutoSupplied.Orders) C
INNER JOIN
GutoSupplied.Products P
ON 0=0
) CP
EXCEPT
SELECT O.Customer_ID, P.Product_ID
FROM GutoSupplied.Orders O
INNER JOIN
GutoSupplied.Products P
ON O.Product_ID = P.Product_ID
ORDER BY
Customer_ID, Product_ID;
------------------------------------------------------------------------------
CUSTOMER_ID PRODUCT_ID
----------- -----------
1 5
2 3
2 4
2 5
3 2
3 5
4 1
4 2
4 3
4 4
10 record(s) selected. Received on Fri Jan 05 2007 - 18:09:55 CET