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

Question

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

Navigation

#1 by (2 votes)

1

I have two tables:

QUIZ

QUIZ_GERAL

I would need to list data from both tables when:

The logged in user ID is the same as the IDCONTA field

OU

When there is a line in the QUIZ table and there is no line in the QUIZ_GERAL table with the user ID and ID of the logged in 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 id 1 does not list the quiz with id 1, since SELECT understands that it already exists, strong> idconta 2.

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

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

mysql select

asked by anonymous 10.08.2016 / 22:34

1 answer

How to set% to integer in google charts Get information from a url using node + express + ejs

score 2 · Answer 1

A simple way to take advantage of the query you have already set up is to change INNER JOIN to RIGHT JOIN. The problem with your query is that INNER JOIN removes from the result values that do not pass in the condition reported in the JOIN:

quiz_geral.idquiz = quiz.id

With RIGHT JOIN, MySQL populates the values not found in the quiz_geral table with NULL like the image below:

Thenjustincludethenullidtestinthequery:

SELECT*FROMquiz_geralRIGHTJOINquizONquiz_geral.idquiz=quiz.idWHERE(quiz_geral.idconta=1ANDquiz_geral.respondido<>quiz_geral.total)OR(quiz_geral.idISNULL)ORDERBYquiz.anoASC,quiz.mesASC

ItwouldevenbebettertoswapRIGHTJOINfortheLEFTjoinasmentionedintheMySQLdocumentation( link ):

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 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.