Problem with writing an SQL Sub Query

Question

I know I must be missing something simple here...but I'm having problems with writing an SQL sub query. Given the following data

user_id question_id answer_text
89          1           value1
89          2           value2
80          2           value2
99          2           value2
96          1           value1
96          2           value2
111        1           value1
111        2           value2

I need to get the user_id's that have BOTH question_id 1 = 'value1' AND have question_id 2 = 'value2'

The results above are generated using this query:

SELECT `User_answer`.`user_id`, `User_answer`.`question_id`, `User_answer`.`answer_text` FROM `user_answers` AS `User_answer` WHERE `User_answer`.`question_id` IN (1, 2) AND `User_answer`.`answer_text` IN ('value1', 'value2')

but of course this returns users that have answered question 1 with "value1" but question 2 with a completely different answer than "value2" (and vice versa).

I need to be able to add in more conditions but I think I can add that if I can get at least these two working.

Answer 1

One method is to use a self join, eg

select a1.user_id, a1.answer_text AS Answer1, a2.answer_text AS Answer2
from user_answers a1 join user_answers a2 on a1.userid=a2.userid and a1.question_id=1     and a2.question_id=2
where a1.answer_text <> a2.answer_text

sorry if the syntax is a little off (It's been a while since I used MySQL), but you get the idea.

Answer 2

I would do this using an inner join like this:

SELECT
  user_id
FROM
  user_answers AS a1
  INNER JOIN user_answers AS a2 ON
    a2.user_id = a1.user_id
    AND a2.question_id=2
    AND a2.answer_text='value2'
WHERE
  a1.question_id=1
  AND a1.answer_text='value1'

Translates to:

1. Find all answers with a question_id of 1 and answer_text of 'value1'
2. For these answers, find corresponding answers with same user id and question_id of 2 and answer_text of 'value2'.
3. Do an inner join, ie throw away those which don't satisfy both.

Answer 3

I'd try doing it without a subquery, like this:

SELECT `User_answer`.`user_id`, 
  MAX(CASE `User_answer`.`question_id` WHEN 1 THEN `User_answer`.`answer_text` END) AS `q1_answer`,
  MAX(CASE `User_answer`.`question_id` WHEN 2 THEN `User_answer`.`answer_text` END) AS `q2_answer`
FROM `user_answers` AS `User_answer` 
WHERE (`User_answer`.`question_id` = 1 AND `User_answer`.`answer_text` = 'value1')
  OR  (`User_answer`.`question_id` = 2 AND `User_answer`.`answer_text` = 'value2')
GROUP BY `User_answer`.`user_id`
HAVING COUNT(DISTINCT `User_answer`.`question_id`) = 2;