Self Join? Or am I wrong?

We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item:

customer     item  
Sally        1   
Sally        2  
Sally        3
John         1  
John         2  
Paul         2
Paul         3

We want to find users who bought "1" and not "3". In this case we only want "John".

No, a self join would not really help, but if your database system has an EXCEPT operator (or something similar, in Oracle it is called MINUS) it would.

Here's how you get your result:

SELECT customer FROM Sales
WHERE item = 1
AND customer NOT IN (SELECT customer FROM Sales WHERE item = 3)

If you have an EXCEPT operator:

SELECT customer FROM Sales WHERE item = 1
EXCEPT
SELECT customer FROM Sales WHERE item = 3

StackExchange Database Administrators Q#80322, answer score: 10