SQL query of all the questions except that the column "correct answers" of the other table is equal to 4

Question

SQL query of all the questions except that the column "correct answers" of the other table is equal to 4

Navigation

#1 by (0 votes)

0

The purpose of the query is to show all the rows in the table "questionsService Operator"; except those in "correctResponse" have a value greater than 3 and except in the table "repliesTestUsuario" does not mention that numberQuestion.

SENSE OF THE APPLICATION TO UNDERSTAND THE CONTEXT:

It is intended that if a user answers a question well 4 times, it is understood that he has learned it, so the program adds up. Then the query should not show the questions if in "correct answers" there is a value greater than 3.

PROBLEM: With the query that I show, I get all the questions that appear in the table AnswersTestUsuario, but not all those that are not mentioned in the table "AnswersTestUsuario".

INQUIRY:

SELECT * 
       FROM preguntasOperarioDeServicios 
       INNER JOIN respuestasTestUsuario 
             ON preguntasOperarioDeServicios.numeroPregunta=respuestasTestUsuario.numeroPregunta 
       WHERE idUsuario LIKE 30 
             AND respuestasTestUsuario.modalidadPreguntas LIKE 'operarioDeServicios' 
             AND respuestasTestUsuario.respuestasCorrectas<4

As an example, since numberQuestion 1 has the answer Correct 4, question 1 should not be selected. But if question 2 and 3 and 4 (also although it is not in this table) and 5 (even if it is not in this table) and 6 (also although it is not in this table), etc ... But since in the table repliesTestUsuario does not make reference to the numberQuestion 4,5,6, etc, the query does not return them to me.

I hope to have explained myself, it is not easy to explain. ;)

mysql sql

asked by r84 27.12.2018 в 21:53

source

1 answer

Pass item from a Listbox to a Label connectcxion problems with the jdbc

score 0 · Accepted Answer

The solution is to subtract a select, another select with the content that we want to discard.

SELECT DISTINCT numeroPregunta 
  FROM preguntasOperarioDeServicios 
 WHERE NOT EXISTS(SELECT numeroPregunta 
                    from respuestasTestUsuario 
                   WHERE preguntasOperarioDeServicios.numeroPregunta=respuestasTestUsuario.numeroPregunta 
                     AND respuestasCorrectas >3)

It works perfect! ;)