We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:
user_id question_id answer_value Sally 1 Pooch Sally 2 Peach John 1 Pooch John 2 Duke
and we want to find users who answer 'Pooch' for question 1 and 'Peach' for question 2, the following SQL will (obviously) not worK:
select user_id from answers where question_id=1 and answer_value = 'Pooch' and question_id=2 and answer_value='Peach'
My first thought was to self-join the table for each answer we are looking for:
select a.user_id from answers a, answers b where a.user_id = b.user_id and a.question_id=1 and a.answer_value = 'Pooch' and b.question_id=2 and b.answer_value='Peach'
This works, but since we allow an arbitrary number of search filters, we need to find something much more efficient. My next solution was something like this:
select user_id, count(question_id) from answers where ( (question_id=2 and answer_value = 'Peach') or (question_id=1 and answer_value = 'Pooch') ) group by user_id having count(question_id)>1
However, we want users to be able to take the same questionnaire twice, so they could potentially have two answers to question 1 in the answers table.
So, now I'm at a loss. What's the best way to approach this? Thanks!