Select with MYSQL when it exists in one table and not in the other

Question

I have two tables:

QUIZ

QUIZ_GERAL

I would need to list data from both tables when:

• The logged in user ID is equal to the IDCONTA field

OR

• When there is a row in the QUIZ table and there is no row in the table QUIZ_GERAL with the IDQUIZ and the IDLOGGED user

So far I’ve done something like this:

$sql1 = $pdo->prepare('SELECT * FROM quiz_geral 
RIGHT JOIN quiz ON quiz_geral.idquiz = quiz.id
WHERE (quiz_geral.idconta = :idLogado) OR (quiz_geral.id IS NULL)
ORDER BY quiz.ano ASC, quiz.mes ASC');

The problem is that for the user with idconta 1 does not list the quiz with id 1, because SELECT understands that it already exists, but it exists for the idconta 2.

How to list all quizes (1,2,3) for each user.

(I simplified the tables and selects well, they are more complex, but my doubt is in this stage that I spoke)

Answer 1

A simple way, taking advantage of the query you have already assembled, is to exchange INNER JOIN for RIGHT JOIN. The problem with your query is that INNER JOIN removes from the result values that do not pass in the JOIN condition:

quiz_geral.idquiz = quiz.id

With RIGHT JOIN, Mysql fills the values not found in the general quiz_table with NULL as the image below:

Then just include the null id test in the query:

SELECT * FROM quiz_geral 
RIGHT JOIN quiz ON quiz_geral.idquiz = quiz.id
WHERE (quiz_geral.idconta = 1 AND quiz_geral.respondido <> quiz_geral.total) OR (quiz_geral.id IS NULL)
ORDER BY quiz.ano ASC, quiz.mes ASC

It would be even better to exchange RIGHT JOIN for LEFT Join, as mentioned in the Mysql documentation (http://dev.mysql.com/doc/refman/5.7/en/join.html):

RIGHT JOIN Works analogously to LEFT JOIN. To Keep code Portable Across Databases, it is Recommended that you use LEFT JOIN Instead of RIGHT JOIN.

And you would have something like:

SELECT * FROM quiz 
LEFT JOIN quiz_geral ON quiz_geral.idquiz = quiz.id
WHERE (quiz_geral.idconta = 1 AND quiz_geral.respondido <> quiz_geral.total) OR (quiz_geral.id IS NULL)
ORDER BY quiz.ano ASC, quiz.mes ASC

Only one addendum, it is interesting to use the field names instead of * in SELECT since you have 2 tables with the same field name (id) and this can generate conflicts in the result.