Re: Curious SQL question
Date: Wed, 03 Jan 2007 15:11:52 GMT
Message-ID: <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.
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 - 16:11:52 CET