How to query in MySQL with two different conditions in WHERE

4

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

They 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 of the WHERE clause ... I need the records of the two within the same array arriving in PHP ... I tried with UNION, with SELECT chained and nothing ... Anyone know an alternative?

    
asked by anonymous 09.12.2014 / 04:11

3 answers

8

I have not checked the entire query but if that's what you're saying I think this 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 placed GitHub for future reference .

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

A AND must satisfy all conditions set then t.idSemestre = '$idSemestre' AND pb.idProfessor = '$idProfessor' must find both the semester searched in the t table and the teacher in the pb table as well.

If you can actually have a teacher fit for the condition in two different tables you can look at the two as you did and join these two queries in WHERE with OR .

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

The ideal is that the semester search comes first because if the database finds nothing 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, it's no use looking at the second, the whole expression will already be false.

In% w /%, the opposite occurs. If you find a teacher in the first sub-expression (first operand) of OR he does not even have to look at the second operand because it is already certain that the expression will be true.

Remember that OR will be applied to all rows obtained by the other WHERE clauses. That's why a suitable index can be helpful in streamlining your selection.

    
09.12.2014 / 04:21
6

The general WHERE syntax is:

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

WHERE condição1 OR condição2

If the condition1 OU condition2 is true, the query will be performed. The term OR means OU .

Please note that you may be combining use of AND and OR . For example:

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

In this case, the condition1 and the condition2 must be true to perform the query or only condition3 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 which case the condition1 must be true and only one of the other two conditions is true ( condition2 or condition3 )

Now you only have to adapt in your query , according to your needs.

    
09.12.2014 / 04:24
0

The UNION was to solve your need, just remove the order by the first SQL and put the UNION between them but, would not be the most correct after all the querys are identical the only difference is your teacher id which ends in unnecessary duplicate code. try using

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

In terms of performance, try to escape OR / NOT because the bank often can end up making an unnecessary table scan.

I hope I have helped.

    
09.12.2014 / 15:23