+------------------+----------+------+-----+---------------------+-------+
| 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 ?
Addition:
The inner selection returns this:
+----------------+--------+
| participant_id | rcount |
+----------------+--------+
| 1 | 2 |
| 6 | 1 |
| 36 | 4 |
| 40 | 4 |
+----------------+--------+
Reason is that participant answer has 1 answers in question 76 and none in question 103. participants 36 and 40 have *4* answers in question 76 and none in 103.
So should find the way to limit the inner select to only the highest date_time or just any single answer.