[SOLVED] Select values that meet different conditions on different rows

Select values that meet different conditions on different rows

Let's say I have a two-column table like this:

userid  |  roleid
--------|--------
   1    |    1
   1    |    2
   1    |    3
   2    |    1

I want to get all distinct userids that have roleids 1, 2 AND 3. Using the above example, the only result I want returned is userid 1. How do I do this?

Solution

SELECT userid
FROM UserRole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;

Just thinking out loud, another way to write the self-join described by cletus is:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid
JOIN userrole t3 ON t2.userid = t3.userid
WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3);

This might be easier to read for you, and MySQL supports comparisons of tuples like that. MySQL also knows how to use covering indexes intelligently for this query. Just run it through EXPLAIN and see "Using index" in the notes for all three tables, which means it's reading the index and doesn't even have to touch the data rows.

I ran this query over 2.1 million rows (the Stack Overflow July data dump for PostTags) using MySQL 5.1.48 on my MacBook, and it returned the result in 1.08 seconds. On a decent server with enough memory allocated to innodb_buffer_pool_size, it should be even faster.

_{To anyone reading this: my answer is simple and straightforward, and got the 'accepted' status, but please do go read the answer given by cletus. It has much better performance.}