+------------------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------+------+-----+---------------------+-------+
| questionnaire_id | int(11) | NO | PRI | 0 | |
| participant_id | int(11) | NO | PRI | 0 | |
| question_id | int(11) | NO | PRI | 0 | |
| answer_id | int(11) | NO | PRI | 0 | |
| date_time | datetime | NO | PRI | 0000-00-00 00:00:00 | |
| freetext | text | NO | | | |
+------------------+----------+------+-----+---------------------+-------+
Currently the filters are combined by generating single sql that should
return the terminal list. Here's the example of generated sql:
SELECT participant_id FROM (
SELECT participant_id, count(participant_id) as rcount
FROM participant_answer
WHERE (
( question_id = 76 AND answer_id in ('20')) OR
( question_id = 103 AND answer_id in ('87'))
)
GROUP BY participant_id
) AS temporarySelectTable where rcount>=2
This should return a list of participant_id's inner select's list of
terminals where rcount>=2.
But this fails because for example, participant 36 has answered the
question 76 4 times so his rcount is actually 4 and he's making it to
the final list anyway.
Problem here is that the inner select should read only one row, not all
4 rows. Any ideas how to select that inner select query to return only
one answer per/answer pair ?