Sougata Nandy Sougata Nandy - 13 days ago 6
SQL Question

How to list products NOT bought by a customer?

I have 3 tables -

customer
,
product
and
customerproduct
(linked table containing customers and the products they bought).

i want to list each customer with the products not bought by him.

Example

Customers

Tom
Jim
Harry


Product

Shampoo
Brush
Shoe
Box


Customerproduct

Jim | box
Jim | brush
Tom | brush
Harry | shampoo


So, my query should display:

Jim | shampoo
Jim | shoe
Tom | shampoo
Tom | shoe
Tom | box
...


I need to use lookup to get my customers and products. Making a separate table CustomersandProducts table and feeding it in manually is not an option.
Surely, there must be a way to get the results using NOT EXISTS, NOT IN, etc?

Answer

First you need to product join customer and product to get all permutation and combination of customer and product. then just lookup in actual customer product table.

Try following query:

select c.cname,p.pname
customers c,
product p
where c.name,p.name not in (
select cp_i.cname,cp_i.pname
from customerproduct cp_i)

This query will be extremely resource sensitive(cpu/io etc.) if number of rows are high in any of table.

another approach:

select c.cname,p.pname
customers c,
product p
where not exists (
select 1
from customerproduct cp_i
where cp_i.cname=c.cname
and cp_i.pname=p.pname)