How to query in Mysql with two different conditions in WHERE

Question

I have two queries that work perfectly separately, but I needed all their records together.

Would be these:

SELECT b.idBanca AS idB, b.DataHora AS dataHora, 
       b.Sala AS sala, t.idTrabalho AS idT, 
       p.Nome AS orientador, a.Nome AS aluno, t.Nome AS trabalho 
FROM Bancas b 
INNER JOIN Trabalhos t ON t.idTrabalho = b.idTrabalho 
INNER JOIN Professores p ON t.idProfessor = p.idProfessor 
INNER JOIN Alunos a ON t.idAluno = a.idAluno 
INNER JOIN ProfessoresBancas pb ON b.idBanca = pb.idBanca 
WHERE t.idSemestre = '$idSemestre' AND pb.idProfessor = '$idProfessor' 
ORDER BY dataHora 

and

SELECT b.idBanca AS idB, b.DataHora AS dataHora, 
       b.Sala AS sala, t.idTrabalho AS idT, 
       p.Nome AS orientador, a.Nome AS aluno, t.Nome AS trabalho 
FROM Bancas b 
INNER JOIN Trabalhos t ON t.idTrabalho = b.idTrabalho 
INNER JOIN Professores p ON t.idProfessor = p.idProfessor 
INNER JOIN Alunos a ON t.idAluno = a.idAluno 
INNER JOIN ProfessoresBancas pb ON b.idBanca = pb.idBanca 
WHERE t.idSemestre = '$idSemestre' AND p.idProfessor = '$idProfessor' 
ORDER BY dataHora

Only what changes is the $idProfessor WHERE... I need the records of the two within the same array arriving in PHP... I tried with UNION, with SELECT chained and nothing... Would anyone know any alternative?

Answer 1

I didn’t check all the query but if that’s what you’re saying I think it solves:

SELECT b.idBanca AS idB, b.DataHora AS dataHora, 
       b.Sala AS sala, t.idTrabalho AS idT, 
       p.Nome AS orientador, a.Nome AS aluno, t.Nome AS trabalho 
FROM Bancas b 
INNER JOIN Trabalhos t ON t.idTrabalho = b.idTrabalho 
INNER JOIN Professores p ON t.idProfessor = p.idProfessor 
INNER JOIN Alunos a ON t.idAluno = a.idAluno 
INNER JOIN ProfessoresBancas pb ON b.idBanca = pb.idBanca 
WHERE t.idSemestre = '$idSemestre' AND 
       (p.idProfessor = '$idProfessor' OR pb.idProfessor = '$idProfessor')
ORDER BY dataHora

I put in the Github for future reference.

You had an interesting idea when using the UNION. In fact what you need is to make a union but there are several ways to do it. This is possible to do through logical operators. A OR is a union, as is the AND that you used is an intersection.

A AND must satisfy all the conditions set out then t.idSemestre = '$idSemestre' AND pb.idProfessor = '$idProfessor' need to find both the semester sought in the table t as the teacher in the table pb also.

If in fact you can have a suitable teacher for the condition in two different tables you can look at both, as you did and unite these two searches in WHERE with the OR.

Note that first you look at a table to find out if the semester meets the need and only then it will look at the teacher at whichever of the two tables. To ensure that these two searches by the teacher are one expression, the parentheses are used.

The ideal really is that the demand for the semester comes first because if the database does not find anything it does not even try to find the teacher in the two tables. After all in a AND If you have the first false operand, no use looking at the second, the whole expression will already be false.

In the OR occurs the opposite. If you find a teacher in the first sub-expression (first operand) of OR he no longer needs to look at the second operand because it is already certain that the expression will be true.

Remember that the WHERE will be applied to all lines obtained by the other clauses of SELECT. For this reason a suitable index can be useful to expedite selection.

Answer 2

The general syntax of WHERE is:

WHERE <condição1> AND/OR <condição2> AND/OR <condição3> ...

WHERE condição1 OR condição2

If the condition 1 OR to condition2 is true the consultation will be held. The term OR means OR.

Note that you may be combining the use of AND and of OR. For example:

WHERE (condição1 AND condição2) OR condição3

In that case the condition 1 and the condition2 needs to be true to perform the query or only to condition 3 to satisfy the condition. Already in this other example:

WHERE condição1 AND (condição2 OR condição3)

Note that parentheses can affect your condition, in that case the condition 1 must be true and only one of the other two conditions be true (condition2 or condition 3)

Now all that remains is to adapt to your query, according to your needs.

Answer 3

UNION was to resolve their need, simply remove the order by from the first SQL and put the UNION between them but, would not be the most correct after the querys are identical the only difference is their teacher id which ends up in duplicate code without need. try to use

WHERE t.idSemestre = '$idSemestre' AND
 (p.idProfessor in ('$idProfessor1','$idProfessor2',....,'$idProfessorN'))

When it comes to performance, try to escape from OR/NOT because the bank in can often end up doing an unnecessary table scan.

I hope I’ve helped.