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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I need to make an appointment where the calls that were not validated are listed. It works like this:

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

I need to filter out all the calls that did not have registered code action 140, for example.

My query looks 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 in the records of his actions there is not one that has the 140 code.

oracle query

asked by anonymous 28.09.2018 / 00:16

3 answers

JSON Regular Expression and Deserialization Dictionary Sending problem via URL (GET)

score 0 · Answer 1

Use the ! = sign to NOT equal or NOT IN values, so it 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)

score 0 · Answer 2

An alternative is to first make a subquery with the calls that have action 140, and then you make a select in the so called not 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 you can also use the WITH clause. This sets the subquery before, and then uses this subquery to query it.

The idea is the same: subquery checks which calls have action 140, then select takes all calls that are not in this subquery (ie , which does not have 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 in SQL Fiddle .

score 0 · Answer 3

Hello,

I think the code below solves your problem because it takes all the calls that there is no associated action 140 in the 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);