Re: Curious SQL question
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 05 Jan 2007 14:17:40 GMT
Message-ID: <86tnh.40823$cz.601735_at_ursa-nb00s0.nbnet.nb.ca>
>>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.
>>
>>Bob Badour wrote:
>>
>>>kvnkrkptrck_at_gmail.com wrote:
>>>
>>>
>>>>-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
>>>>contain any personal identifying information. In such a case, it
>>>[more irrelevant fantasies snipped]
>>
Date: Fri, 05 Jan 2007 14:17:40 GMT
Message-ID: <86tnh.40823$cz.601735_at_ursa-nb00s0.nbnet.nb.ca>
Walt wrote:
> "Guto" <gutomore_at_gmail.com> wrote in message > news:1167937176.490082.262510_at_s80g2000cwa.googlegroups.com... >
>>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.
>>
>>Bob Badour wrote:
>>
>>>kvnkrkptrck_at_gmail.com wrote:
>>>
>>>
>>>>-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 -- >>>>>I disagree. Perhaps his is a data-mining database which does not
>>>>>look at all the assumptions we have to make
>>>>>
>>>
>>>[Joe's irrelevant fantasies snipped]
>>>
>>>
>>>Could someone clue Joe into the fact that this newsgroup is
>>>comp.databases.theory and not comp.databases.syntax.sql ?
>>>
>>>(Speaking of highly detailed design recommendations on the basis of no
>>>information whatsoever... cuckoo... ::rolls eyes:: )
>>>
>>>
>>>
>>>>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.
>>>
>>>Indeed. Or the original poster might have changed the names to protect
>>>the guilty (and comply with an NDA.) The real question might have
>>>nothing to do with customers and orders per se.
>>>
>>>In many respects, it would help Guto if he thought about this problem
>>>entirely in the abstract without reference to 'real-world' entities.
>>>
>>>His relations could just as easily have been:
>>>
>>>R1 = { {Y: ydom, N: ndom} }
>>>
>>>R2 = { {X: xdom, Y: ydom} }
>>>
>>>How could he identify for each X in R2 all the Y's in R1 not associated
>>>with X in R2 ?
>>>
>>>
>>>
>>>>As has been pointed out already, the problem is still easily resolved
>>>>by projecting customer_id and crssing that against products.
>>>
>>>Indeed.
>>>[more irrelevant fantasies snipped]
>>
> > Wouldn't you want to assign different product_id's to different kinds of > PIZZA? > It could depend on what set of toppings, and what set of cheeses go on the > pizza?
After 4 beers, who cares what's on the pizza? Received on Fri Jan 05 2007 - 15:17:40 CET