Doubt - Query (Do not bring a certain record)

0

Good afternoon, people!

Next, below is a query where it was to bring all the calls that have the description '' Block Customer - Defaulter '' and with id = 572 (clients blocked), however, in that query there are some clients with already been unblocked, or you already have a call with another description. I wanted to know how to bring only records with locked clients without any unlocked calls. Below is the query I tried.

SELECT  DISTINCT
*
FROM Tarefa T
WHERE 
T.TarID in(select TarID from Tarefa where T.TarTitulo = 'Bloquear Cliente - Inadimplente' AND T.TarTipID = 572
and T.TarID not in (select TarID from Tarefa where TarTipID = 574 and TarTitulo = 'Desbloqueio Cliente'))
    
asked by anonymous 20.07.2017 / 22:46

1 answer

2

You can use the EXISTS clause together with NOT to discover clients that have not called unlock:

SELECT DISTINCT *
  FROM Tarefa T
 WHERE T.TarTitulo = 'Bloquear Cliente - Inadimplente'
   AND T.TarTipID = 572
   AND NOT EXISTS(SELECT 1
                    FROM Tarefa t2
                   WHERE t2.TarTitulo = 'Desbloqueio Cliente'
                     AND t2.TarTipID = 574
                     AND t2.usuidcliente = t.usuidcliente)
    
20.07.2017 / 22:51