How to find people who bought one product but NOT another?

Question

Hi,

I have a table linking customers to previous purchases:

RecID   CustID   ProdID
  1       20      105
  2       20      300
  3       31      105
  4       45      105
  5       45      300
  6       45      312

I'd like to get a list of CustIDs that bought Item 105, but did NOT but Item 300.

In this case, CustID 31.

I can't seem to do it with selects and joins. I am stumped!

I sure would appreciate some help from experienced SQL folks.

Thank you!

Answer 1

There are three common approaches. Here is a LEFT JOIN approach:

SELECT T1.CustID
FROM yourtable T1
LEFT JOIN yourtable T2 ON T1.CustID = T2.CustID AND T2.ProdId = 300
WHERE T1.ProdId = 105
AND T2.ProdId IS NULL

Here is NOT IN:

SELECT CustID
FROM yourtable 
WHERE ProdId = 105
AND CustID NOT IN
(
    SELECT CustID
    FROM yourtable 
    WHERE ProdId = 300
)

Or you could use NOT EXISTS:

SELECT CustID
FROM yourtable T1
WHERE ProdId = 105
AND NOT EXISTS
(
    SELECT NULL
    FROM yourtable T2
    WHERE T2.ProdId = 300
    AND T2.CustID = T1.CustID
)

Which has better performance depends on which database and version you are using.

---

For SQL Server the best is to use either NOT IN or NOT EXISTS:

In SQL Server, NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL. They produce the safe efficient plans with some kind of an Anti Join.

LEFT JOIN / IS NULL is less efficient, since it makes no attempt to skip the already matched values in the right table, returning all results and filtering them out instead.

Source:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

Answer 2

SELECT CustID from T T1
WHERE  ProdID = 105
  AND  NOT EXISTS (SELECT 1 from T T2
                   WHERE  T2.ProdID = 300
                   AND    T2.CustID = T1.CustID)

Answer 3

A couple of different approaches.

select CustID from t where ProdID=105
except
select CustID from t where ProdID=300

or

select CustID 
from t 
where ProdID in (105,300)
group by CustID 
having max(ProdID)=105

Answer 4

SELECT PPT.CustID FROM PreviousPurchasesTable PPT
WHERE PPT.ProdID = 105
AND PPT.CustID NOT IN (SELECT PPT2.CustID FROM PreviousPurchasesTable PPT2 WHERE PPT2.ProdID = 300)

Answer 5

Thanks!

I am the original author of the question.

Mark Byers second example with NOT IN works great! (I did not try others after this one worked for me).

His first example with LEFT JOIN did not return any CustIDs...I think I copied it correctly and used the proper table names and column names. So I don't know why it did not work for me.

Thanks again to all who were so kind as to take the time to write out some SQL for me.

I had to create a new account to leave a comment (I could not login with the account where I created this yesterday, and the password recovery said it couldn't find me)