How to query a join table so that multiple criteria are met?

Question

I have a table with 2 columns (see below). A member can have multiple responses to a question:

RESPONSES
---------
member_id  INT
response_id  INT

SAMPLE DATA
member_id -- response_id
    1     --     3
    1     --     5
    2     --     1
    2     --     5
    2     --     9
    3     --     1
    3     --     5
    3     --     6

What I need to do is query the table for member that meet ALL response criteria. For example I need to select all members that have a response_id of 1 AND 5. I am using the following query:

SELECT DISTINCT member_id 
FROM responses 
WHERE response_id = 1 AND response_id = 5

I would expect to get back member_id's 2 and 3. However I am getting nothing returned. I used EXPLAIN and it shows there is an error in my where query. What am I doing wrong?

Also, is there a function similar to IN where all the criteria must be met in order to return true?

Answer 1

This should work:

SELECT member_ID
FROM responses
WHERE response_ID IN (1,5)
GROUP BY member_ID
HAVING COUNT(DISTINCT response_id) = 2

You need to count the number of records returned which is equal to the number of values supplied in your IN clause.

SQLFiddle Demo