sql experts
- Started
- Last post
- 4 Responses
- kinetic
can anyone help me augment this query:
SELECT user_id AS pass_id, COUNT( question_id ) AS correct
FROM contest_answers
WHERE question_id
IN ( 110, 111, 112, 113 )
GROUP BY user_id
HAVING correct = '4'its for a voting contest...so if someone voted for answers with ids 110, 111, 112 and 113, they could win...the problem is the above query will still select someone if they voted for 110, 111, 112 and 114 one day and 110, 111, 113 and 200 another day
so i need to select based on date they submitted which is a field called answer
if they selected those 4 numbers on a certain / unique date, then they can win
- flavorful0
SELECT user_id AS pass_id, COUNT( question_id ) AS correct
FROM contest_answers
WHERE
( (question_id IN ( 110, 111, 112, 113 )) AND (question_id NOT IN (110, 111, 113, 200)))
GROUP BY user_id
HAVING correct = '4'----
?
This should only include their "correct" answers and should bar any "incorrect" answers.
- flavorful0
Perhaps add another AND where clause depending on today's date?
Or to only accept answers on a given date as well?
You can add subqueries as well to allow correct users to continue but bar them if they ever had an incorrect answer.
If you need help with that let me know.
- kinetic0
i got one that works...but unfortunately the production machine doesn't run the latest mysql so my query is bunk
ill test yours out though...thanks man :)
- kinetic0
damn, that didn't work
basically the problem my query has is this
lets say the correct answers are 1,2,3,4
on 2006-07-01 the user votes 1,2,3,5
on 2006-07-02 the user votes
1,7,9,10my query will take the fact that they have a total of 4 right even though they where voted on different days...so i need to add another where clause to that query but im not sure how to go about that....i tried a sub select, but no go (because of mysql 3.23)