Re: Curious SQL question

"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 --
> > >>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]
>

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? Received on Fri Jan 05 2007 - 15:01:40 CET