SELECT multiple rows WHERE matching two conditions

Question

This would be better to show on my example:

I have table, where are stored users answers from one big form. Each form has 139 questions. These questions are stored in different table, joined when needed with questionID. For each user, there is an ID. I now need to make filters, to show only users matching one or more answers on specific questions.

For example, i want users, where question 14 has answer "yes", question 54 is not empty and question 100 is bigger than 10. This is how the table looks:

**userID** | **questionID** | **answer**

1            14               "yes"
1            54               "something"
1            100              "9"
2            14               "no"
2            54               "north
2            100              "50"
3            14               "yes"
3            54               "test"
3            100              "12"

as result i want only the userID 3 returned, because it meets all conditions.

This would be easy to reach with ColdFusion, as it allows query in queried results, but in PHP i haven't found any way. It is important to have chance to add random number of questions, not only three as in this example.

Answer 1

Try,

SELECT userID
FROM tableName
WHERE   (questionID = 14 AND
        answer = 'yes' ) OR
        (questionID = 54 AND
        answer <> 'empty') OR
        (questionid = 100 AND
        answer > 10)
GROUP BY userID
HAVING COUNT(*) = 3

SQLFiddle Demo

Answer 2

SELECT q.userID
  FROM questions q
  JOIN questions qq ON qq.userID=q.userID
       AND qq.questionID='54' AND qq.answer IS NOT NULL
  JOIN questions qqq ON qqq.userID=q.userID
       AND qqq.questionID='100' AND qqq.answer > 10
 WHERE q.questionID=14
   AND q.answer = 'yes'

Answer 3

You can try :

SELECT questionID, answer
FROM table
WHERE ( questionID = 14 AND answer = 'yes' ) 
OR ( questionID = 54 AND answer != '' )
OR ( questionID = 100 AND answer > 10 );