MySQL Query to find customers who have ordered two specific products

Question

I'm having trouble coming up with a query that will find all customers who have purchased both PROD1 and PROD2.

Here's a pseudo-query that kind of looks like what I want to do: (obviously this wouldn't work)

SELECT COUNT(DISTINCT userid) 
  FROM TRANSACTIONS 
 WHERE product_id = 'prod1' 
   AND product_id = 'prod2'

So basically I'm trying to get a count of the number of distinct userids that have a transaction in the transactions table for both product_id 'prod1' and 'prod2'. Each transaction is stored in a row in the transactions table.

Answer 1

I do this type of query in the following way:

SELECT COUNT(DISTINCT t1.userid) AS user_count
  FROM TRANSACTIONS t1
  JOIN TRANSACTIONS t2 USING (userid)
 WHERE t1.product_id = 'prod1' 
   AND t2.product_id = 'prod2';

The GROUP BY solution shown by @najmeddine also produces the answer you want, but it doesn't perform as well on MySQL. MySQL has a hard time optimizing GROUP BY queries.

You should try both queries, analyzing the optimization with EXPLAIN, and also run some tests and time the results given the volume of data in your database.