Group By Questions

Question

Here is a table of profile answers:

profile_id | answer_id
----------------------
1                1
1                4
1               10

Here is a table which contains a list of responses by poll respondents:

user_id | answer_id
-------------------
1            1
1            9
2            1
2            4
2           10
3           14
3           29

I want to return a list of users whose answer was in (6,9) but also in(1,10), basically all of the answers that match profile 1.

How can I write this select query?

I tried the following, but apparently I don't quite understand how group by works:

SELECT DISTINCT  [user_id]
FROM             [user_question_answers] a
GROUP BY         a.[user_id]
HAVING           a.[answer_id] IN (6,9)
AND              a.[answer_id] IN (1,10)

EDIT: Return user_id 1 only

Answer 1

Your query is close . . .

SELECT           [user_id]
FROM             [user_question_answers] a
GROUP BY         a.[user_id]
HAVING           max(case when a.[answer_id] IN (6,9) then 1 else 0 end) = 1
AND              max(case when a.[answer_id] IN (1,10) then 1 else 0 end) = 1