2
I’m making a hotsite with a quiz (questions and answers), with more than 3000 users. The question is in the list of quizzes for each user, I have two tables (simplified to focus on my question):
QUIZ: (which stores all quizes created by the admin, the field total is the total of questions of each quiz)
QUIZ_GERAL (which is created every time a user starts a quiz, field answered is how many questions the user has already answered in this quiz)
When I list to the user the quizes that he has already finished, I have no problems:
SELECT quiz.id AS quizid, quiz.mes, quiz.ano, quiz.total, quiz_geral.respondido FROM quiz_geral
INNER JOIN quiz ON quiz_geral.idquiz = quiz.id
WHERE (quiz_geral.idconta = :idLogado AND quiz_geral.respondido = quiz.total)
ORDER BY quiz.ano ASC, quiz.mes ASC
The problem is time to list the quizzes NOT finished and NOT started, because I have to list them in a single query, ordered by month/year, so far I did so:
SELECT quiz.id AS quizid, quiz.mes, quiz.ano, quiz.total, quiz_geral.respondido FROM quiz_geral
RIGHT JOIN quiz ON quiz_geral.idquiz = quiz.id
WHERE (quiz_geral.idconta = :idLogado AND quiz_geral.respondido <> quiz.total)
OR (quiz_geral.id IS NULL)
ORDER BY quiz.ano ASC, quiz.mes ASC
Until you list the started and unfinished quizes, the problem is to list the not started quizzes (which are not yet in the table QUIZ_GERAL), in the example table I put here, the way I did, would not be listed for the user with idconta 100 o quiz with id 1, because the line OR (quiz_geral.id IS NULL) would not enter in this case, since it exists in the table, but with another idconta.
How to do this second query correctly?