CODEWITHSUNDEEP
mysql
Arunkumar Muthuvel
Arunkumar Muthuvel

Reputation: 433

Need to find which customer are using product 1 and 2 and not yet used product 3

Need to find which customer are using product 1 and 2 and not yet used product 3

pls check the below mysql data

mysql> select product,customer from test1 where product in (1,2,3);

+------+------+
| product | customer |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    1 |    5 |
|    1 |    6 |
|    1 |    7 |
|    1 |    9 |
|    1 |    8 |
|    2 |    1 |
|    2 |    3 |
|    2 |    4 |
|    2 |    6 |
|    2 |    7 |
|    2 |    8 |
|    2 |    9 |
|    3 |    4 |
|    3 |    5 |
|    3 |    6 |
+------+------+
19 rows in set (0.00 sec)

Upvotes: 0

Views: 33

Answers (1)

Penina
Penina

Reputation: 236

There are quite a few ways to do this and efficiency will vary depending on your indexes. One easy way to do this is with joins. You can join the table to itself with an inner join to get customers that have more than 1 product and then use an outer join to make sure the customer does not have a different product.

Here's a code snippet:

SELECT customersWithProduct1.customer
FROM 
    test1 customersWithProduct1
    INNER JOIN test1 customersWithProduct2 ON customersWithProduct1.customer = customersWithProduct2.customer
    LEFT OUTER JOIN test1 customersWithProduct3 ON customersWithProduct1.customer = customersWithProduct3.customer AND customersWithProduct3.product = 3
WHERE 
    customersWithProduct1.product = 1
    AND customersWithProduct2.Product = 2
    AND customersWithProduct3.customer IS NULL

Upvotes: 1

Related Questions