CODEWITHSUNDEEP
mysqlsqldatabase
Severin
Severin

Reputation: 1075

How to find a result with multiple different values on same column?

Imagine that we have a database with a logs table and types table. I want to do a query where I figure out if UserX has entries for certain types of logs. Let's say that UserX has logged type_1 and type_2, but not type_3. I want to write a simple query to see if this is true or false.

At first I tried something like:

SELECT * FROM logs AS l
INNER JOIN types AS t
ON t.id = l.type_id
WHERE t.name = "type_1"
AND t.name = "type_2"
AND t.name != "type_3";

But I quickly realised that it was not possible to do it like this, since t.name cannot have multiple values. I have tried a bunch of different approaches now, but cannot seem to find the one right for me. I'm sure the solution is fairly simple, I just don't see it at the moment.

Hope someone can point me in the right direction.

I have made a simple test database in this fiddle, to use for testing and example: https://www.db-fiddle.com/f/nA6iKgCcJwKnXKsxaNvsLt/0

Upvotes: 0

Views: 142

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I do not recommend using count(distinct) for this purpose. It can be expensive. I would simply do:

SELECT l.userId
FROM logs l INNER JOIN
     types t
     ON t.id = l.type_id
WHERE t.name IN ('type_1', 'type_2', 'type_3')
GROUP BY l.userId
HAVING SUM(t.name = 'type_1') > 0 AND  -- at least one
       SUM(t.name = 'type_2') > 0 AND  -- at least one
       SUM(t.name = 'type_3') = 0 ;    -- none

Upvotes: 1

fancyPants
fancyPants

Reputation: 51928

You can do it like Vamsi, but if you prefer an easier to understand SQL then you can do it like this:

SELECT * FROM logs AS l
INNER JOIN types AS t
ON t.id = l.type_id
WHERE true 
AND EXISTS (SELECT 1 FROM logs ll WHERE l.user_id = ll.user_id AND type_id = 1)
AND EXISTS (SELECT 1 FROM logs ll WHERE l.user_id = ll.user_id AND type_id = 2)
AND NOT EXISTS (SELECT 1 FROM logs ll WHERE l.user_id = ll.user_id AND type_id = 3)

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

One option with conditional aggregation.

SELECT l.userID
FROM logs AS l
JOIN types AS t ON t.id = l.type_id
GROUP BY l.userID
HAVING COUNT(DISTINCT CASE WHEN t.name IN ('type_1','type_2') THEN t.name END) = 2
AND COUNT(DISTINCT CASE WHEN t.name = 'type_3' THEN t.name END) = 0

Upvotes: 2

Related Questions