Filtering rows in a many to many mapping table

Question

If I have a mapping table that looks something like this:

| entity_id | variable |
| --------- | -------- |
|     1     |   2010   |
|     1     |   M      |
|     1     |   MA     |
|     2     |   2010   |
|     2     |   F      |
|     2     |   MA     |
|     3     |   2011   |
|     3     |   M      |
|     3     |   MA     |
|     4     |   2011   |
|     4     |   F      |
|     4     |   MA     |
|     5     |   2010   |
|     5     |   M      |
|     5     |   NY     |
|     6     |   2010   |
|     6     |   F      |
|     6     |   NY     |
|     7     |   2011   |
|     7     |   M      |
|     7     |   NY     |
|     8     |   2011   |
|     8     |   F      |
|     8     |   NY     |

What would be the simplest query to find which entity_ids match all in a set of variables?

Matching any variable in a set is simple, with something like:

SELECT entity_id FROM table WHERE variable IN ('2010', 'MA')

But what if I want to find all entity_ids that map to both '2010' and 'MA'?

Ideally this would be a generic SQL query, but if special features/functions need to be used, assume MySQL.

Answer 1

SELECT entity_id 
FROM table 
WHERE variable IN ('2010', 'MA') 
GROUP BY entity_id 
HAVING count(*) = 2