Re: Curious SQL question

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

Carlos,
You've already got several correct replies, so you don't need this one. I just toss it out there as an illustration of MINUS.

select distinct

     orders.customer_id,
     products.product_id

    orders,
    products
minus
select distinct

    customer_id,
    product_id
from

    products;

The first select esablishes a "universe of discourse" for the query by joining every customer with every product. The second select excludes products that a customer did buy.

I wouldn't necessarily write it this way in a real situation. Received on Fri Jan 05 2007 - 17:43:33 CET