I'm doing a hotsite with a quiz (questions and answers), with more than 3000 users. The question is in the list of quizes for each user, I have two tables (I simplified to focus on my doubt):
QUIZ: (which stores all the quizes created by admin, the total field is the total quiz questions)
QUIZ_GERAL(whichiscreatedeverytimeauserstartsaquiz,answeredfieldishowmanyquestionstheuserhasalreadyansweredonthisquiz)
When I'm going to list the quizes it 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 when it is time to list the quizes NOT ended and NOT started, as I have to list them in a single query, sorted by month / year, so far I have done this:
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 the right list of quizes started and not finished, the problem is to list the uncommented quizes (which are not yet in the QUIZ_GERAL table), in the example table I put here, the way I did , it would not be listed for the user with id 100 the quiz with id 1, since the line OR (quiz_geral.id IS NULL) would not fit in this case, since it exists in the table but with another idconta .
How to do this second query correctly?