How to consult register that does not contain a certain code?

Question

I need to make an appointment to list calls that haven’t been validated. It works that way:

A call is a support request, in which several actions occur, and all are recorded in an auxiliary table.

I need to filter every call that nay had code action 140 registered, for example.

My appointment was like this:

select chamados from tb_chamados 
inner join tb_acoes on tb_acoes.cod_chamado = tb_chamados.cod_chamado
where tb_acoes.acao <> 140

The problem is that the result of this query only ignores the records where the action is equal to 140 and brings the others.

But I want the query to bring me the calls where inside his stock records there is no code 140.

Answer 1

Hello,

I think the code below solves your problem because it takes all the calls that do not exist an action 140 associated in table tb_acoes.

select chamados 
  from tb_chamados ch
 where not exists (select 1
                 from tb_acoes ac
                where ch.cod_chamado = ac.cod_chamado
                  and ac.acao = 140);

Answer 2

Use the signal != for NOT Equal or NOT IN (Values), for DOES NOT contain these values.

select chamados from tb_chamados 
inner join tb_acoes on tb_acoes.cod_chamado = tb_chamados.cod_chamado
where tb_acoes.acao != 140

Or

select chamados from tb_chamados 
inner join tb_acoes on tb_acoes.cod_chamado = tb_chamados.cod_chamado
where tb_acoes.acao NOT IN(140)

Answer 3

An alternative is first you make a subquery with the calls that have the action 140, and then you make a select in the calls that nay are in this subquery:

select * from tb_chamados
where cod_chamado not in -- chamados que não estão na lista abaixo (não tem ação 140)
(
  -- chamados que tem alguma ação 140
  select cod_chamado from
  (
    select cod_chamado, count(*) from tb_acoes
    where acao = 140
    group by cod_chamado
    having count(*) > 0
  )
)

---

In Oracle it is also possible to use the clause WITH. With that you define the subquery before, and then uses this subquery to consult.

The idea is the same: the subquery checks which calls have action 140, then the select picks up all the calls that are not in this subquery (that is, that does not have the action 140):

with chamados_140 as
(    
  -- chamados que tem alguma ação 140
    select cod_chamado, count(*) from tb_acoes
    where acao = 140
    group by cod_chamado
    having count(*) > 0
)
-- escolher os chamados que não estão em chamados_140 (não tem a ação 140)
select * from tb_chamados
where cod_chamado not in
  (select cod_chamado from chamados_140);

See working on SQL Fiddle.