CODEWITHSUNDEEP
mysqlsqlset
Tom
Tom

Reputation: 7951

MySQL - select groups having values covering a set

I have two tables:

CREATE TABLE user (
    user_id INT PRIMARY KEY,
    ...
);
CREATE TABLE action (
    action_id INT PRIMARY KEY,
    user_id INT FOREIGN KEY REFERENCES user(user_id),
    action_type TINYINT,
    ...
);

Each time a user performs a particular action, a row is inserted into the action table.

Now I want to find all users who have performed all of some set of actions. Something like this:

SELECT user_id
FROM user, action
HAVING SET(action_type) INTERSECT (0,3,4,5) = (0,3,4,5);

But of course I've just made up that last line. Is there a good way of doing this in SQL?

Upvotes: 2

Views: 808

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270051

This is an example of a set-within-sets query. I like to solve them using group by and having, because this is a very flexible:

SELECT user_id
FROM action a
WHERE action_type IN (0, 3, 4, 5)
GROUP BY user_id
HAVING COUNT(DISTINCT action_type) = 4;

Upvotes: 3

juergen d
juergen d

Reputation: 204794

4 in the `having clause is the number of actions you have in your set

SELECT u.user_id
FROM user u
JOIN action a on u.user_id = a.user_id
WHERE action_type IN (0,3,4,5)
GROUP BY u.user_id
HAVING count(distinct a.action_type) = 4

Another way would be

SELECT u.user_id
FROM user u
JOIN action a on u.user_id = a.user_id
GROUP BY u.user_id
HAVING sum(a.action_type = 0) > 0
AND sum(a.action_type = 3) > 0
AND sum(a.action_type = 4) > 0
AND sum(a.action_type = 5) > 0

Upvotes: 1

Related Questions