Re: Curious SQL question

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 03 Jan 2007 15:11:52 GMT
Message-ID: <YIPmh.40019$cz.591470@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.

As Lennart pointed out, the missing piece is a join. When Guto sits back and thinks about it, he will realize the product a customer did buy cannot double as a product the customer didn't buy, which means he needs to bring in another product somehow.

> Youy are probably going to want a Customer table sometime in the future. Why > not model it now? Received on Wed Jan 03 2007 - 09:11:52 CST