I have a table of technical calls and a table of calls. Each 'CALL' has several 'CALLS' and it is in the calls that the field defines the status of the call (Open, Closed, In Progress, Suspended, etc ...)
Currently, when I need to list only open calls, I'm looking for ALL calls and for each call I make a new query checking if the last call is closed or not. This practice is not correct. How can I, in a single query, only search for calls in which the last service is not CLOSED?
Without defining your tables you can only suggest a path to the solution. Something like:
SELECT chamados.* FROM chamados
WHERE NOT EXISTS (SELECT * FROM atendimentos WHERE atendimentos.cliente = chamados.cliente AND atendimentos.status = 'Fechado';
Use the inner join.
SELECT c.* FROM chamados c INNER JOIN atendimentos a USING(id_chamado) WHERE a.status <> "Fechado";
In the above case, in the two tables there must be the field "call_id" or the field that you use to make the connection between the two tables.
/ \ theory about all joins.
using:
select campos
from tabelapai
inner join tabelafilha
on tabelapai.campo = tabelafilha.campo
and tabelapai.campo = tabelafilha.campo
where tabelapai.campo = xx
and tabelapai.campo = yy;
you are passing 1 command to MySQL "select fields from tabelapai AND ADD THE CHILDREN TABLE when fields of the parent and child table are the same (ID keys) WHERE THE FIELD OF THE PARENT TABLE IS X
select chamados.dados, atendimentos.dados(atendimentos.situacao,chamados.cliente)
from chamados
INNER JOIN atendimentos
ON atendimentos.codigo_chamado = chamados.codigo_chamado;
WHERE chamados.situacao = x ;
Just change to the code that you want, easy to understand, I recommend reading for different types of JOIN (I would use leftjoin in your case to ONLY bring calls that have a closed situation BUT DO NOT FORGET TO CALL CALLS THAT ARE NO SITUATION SOME).