Re: Curious SQL question

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news:YIPmh.40019$cz.591470_at_ursa-nb00s0.nbnet.nb.ca...
> Walt wrote:
>
> > "Guto" <gutomore_at_gmail.com> wrote in message
> > news:1167826602.405469.312020_at_48g2000cwx.googlegroups.com...
> >
> >>Hi There!
> >>If I have two tables:
> >>
> >>Products
> >>-Product_ID
> >>-Product_Name
> >>
> >>and
> >>
> >>Orders
> >>-Customer_ID
> >>-Product_ID
> >>
> >>
> >>How can I find out what products, each customer DIDN'T bought?
> >>
> >>I tried lots of SQL sentences, but I can onlye get it solved, through a
> >>Stored Procedure.
> >>
> >>
> >>The obvius does not work:
> >>
> >>Select Customer_ID, Product_ID from Orders
> >>where Product_ID not in ( select Product_id from Products )
> >>
> >>Any Tips?
> >>
> >>Regards
> >>Carlos Augusto
> >
> > Look up the MINUS operator or the WHERE NOT EXISTS construct. There are
> > other ways, but start with these.
>
> Hi Walt,
>
> I think if you look at the problem a little more closely, you will
> realize that MINUS and NOT EXISTS have nothing to do with the problem;
> they are more-or-less synonyms for NOT IN, which the original poster is
> already using.

Oops. Yes, you are right. His problem is that he was using the wrong subquery.

>
> As Lennart pointed out, the missing piece is a join.

Also right. Guto needs to join all products with all customers (cartesian join, sigh) and then put in a NOT IN
with a set of product IDS the individual customer DID buy.

It's probably worth the exercise for Guto to learn how MINUS works. Just for background knowledge. Received on Wed Jan 03 2007 - 19:46:39 CET