CODEWITHSUNDEEP
mysql
AaplMike
AaplMike

Reputation: 353

Mysql SELECT when there are multiple values for the search term

I'm trying to select all user_id's from the following table (excerpt) where product_id '3' is not associated with the user_id at all.

user_id product_id  status  terms_id
100         3           1       10
100         22          0       0
100         402         0       20
101         22          1       10
101         68          1       0
101         120         1       20
201         22          0       0
201         3           1       10

In this example, only user_id 101 should be selected because it doesn't have product_id 3 at all. Each user_id can have multiple entries.

I've tried the following, but it incorrectly selects all the user_id's

SELECT distinct user_id FROM table WHERE product_id <> 3

How could I accomplish this? The actual table has more than 3.5 million rows. Thanks!

Upvotes: 0

Views: 44

Answers (1)

Nick
Nick

Reputation: 147166

You can use a NOT EXISTS subquery to check that the user has no associated row with product_id = 3:

SELECT DISTINCT user_id
FROM `table` t1
WHERE NOT EXISTS (SELECT * FROM `table` t2 WHERE t2.user_id = t1.user_id AND t2.product_id = 3)

Output

101

Demo on dbfiddle

An alternate solution is to GROUP BY user_id and to assert that the count of rows with product_id = 3 is 0:

SELECT user_id
FROM `table`
GROUP BY user_id
HAVING SUM(product_id = 3) = 0

Demo on dbfiddle

Upvotes: 3

Related Questions