SQL - finding customer with 2 specific products bought

user_8708054 0
Viewed 9933

I need some help with the following in SQL:

I have 3 tables which have the following data:

table name:customer

customer ID - 1,2,3,4,5,6

customer Name - customer 1, customer 2,customer 3, customer 4, customer 5, customer 6

table name: transactions

Transaction ID -1, 2,3,4,5,6,7,8

Product ID - 2,2,3,4,2,1,4,2

customer ID - 1,2,4,4,5,6,2,5

table name: product

Product ID - 1,2,3,4

product Name - product 1, product 2, product 3, product 4

I want to know which customer(s) bought product 3 and 4 - the result should be just the customer with an ID of 4.

I have the lines below, however it will only work for 3 OR 4 due to the IN function which means displays customer ID 4 and customer ID 2. I am not sure where to use the AND function in this scenario

select distinct c.customer ID
              , c.customer Name 
  FROM transactions t 
  LEFT 
  JOIN  customer c 
    on c.customer ID = t.customer ID 
  LEFT 
 JOIN product p 
    on p.product ID = t.product ID
 where p.product ID IN (3,4)`

Thanks

Vishal

mysql sql
5 Answers

Straight forward: select customers that are both in the set of product 3 buyers and product 4 buyers:

select * 
from customer
where customer_id in (select customer_id from transactions where product_id = 3)
  and customer_id in (select customer_id from transactions where product_id = 4);

It is often faster, however, to query the transaction table only once (by aggregating it by customer).

select * 
from customer
where customer_id in 
(
  select customer_id 
  from transactions 
  where product_id in (3,4)
  group by customer_id
  having count(distinct product_id) = 2
);
user_22707620

use joins:

Select c.CustomerName
from Customer c join Transacation t
on c.Customer_ID = t.Customer_ID
where Product_ID in (3,4)
group by c.CustomerName
having count(distinct Product_ID) = 2
user_38384980

Another way, but not so optimal:

select * 
from customer
where customer_id in (select customer_id from transactions where product_id = 3
INTERSECT
select customer_id from transactions where product_id = 4);
user_44219170

One possible query to do that is the following. The inner subquery extracts only customers who have the two products (see last WHERE A.RC=2), simulating the sort of "and condition" you need.

SELECT DISTINCT A.customer_ID, C.customer_Name
FROM (SELECT customer_ID, COUNT(DISTINCT product_ID) AS RC
      FROM transactions
      WHERE t.product ID IN (3,4)
      GROUP BY customer_ID) A
INNER JOIN transactions t ON A.customer_ID = t.customer_ID
LEFT JOIN customer c on c.customer ID = A.customer ID 
LEFT JOIN product p on p.product ID = A.product ID
WHERE A.RC=2 AND t.product ID IN (3,4)
user_76787890

You need this instead of IN

 where p.product ID = '3' AND p.product ID ='4'

IN uses the OR logic between values thats why you're returning both

user_84127840
Related