Re: Curious SQL question

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");

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 ( 3,1);
insert into orders values ( 3,3);
insert into orders values ( 3,4);

--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 --
> >>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:: )
>
>
> > 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.
>
> 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]
Received on Thu Jan 04 2007 - 19:59:36 CET