Select records from a table where the record of a related table is a specific value

Question

I have a table of technical calls and a table of appointments. Each 'CALL' has several 'CALLS' and it is in the calls that is the field that defines the status of the call (Open, Closed, In progress, Suspended, etc...)

Currently, when I need to list only open calls, I am seeking ALL calls and for each call I make a new query checking whether the last call is closed or not. This practice is not correct. How can I, in a single query, search only the calls in which the last answer is not CLOSED?

Answer 1

Without the definition of their tables one 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';

Answer 2

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 "id_called" field or the field you use to make the link between the two tables.

Answer 3

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

/\ 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 the table AND ADD THE CHILD TABLE when parent and daughter table fields are equal ( identification keys ), WHERE THE PARENT TABLE FIELD 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 you want, easy to understand, recommend reading for different types of JOIN ( I would use leftjoin in your case to bring only those calls that have closed situation but do not fail to bring calls that are without any situation).