Mount an sql query filtering data

Question

I am trying to assemble a report, where I need to list deleted documents, I have the following sql statement:

SELECT id,nf,status,data FROM documentos

For example I have the following results:

When I list the excluded he gives me the following result:

 SELECT id,nf,status,data FROM documentos WHERE status LIKE ('%EXCLUIDO%')

However I should not consider the document whose nf is 000002, because it was then related so it cannot appear in the sql result, should show only the nf 000001.

Could someone help me?

Answer 1

SELECT id,nf,status,data FROM documentos WHERE status = 'EXCLUIDO' and NF <> '000002'

I put the status with the sign = because the query is lighter without using like.

Answer 2

Use not exists to check for Nfs that are more than one status.

SELECT id,nf,status,data 
FROM documentos d1
WHERE status LIKE ('%EXCLUIDO%')
and not exists (SELECT 1 FROM documentos d2 
                         where d1.nf = d2.nf 
                         and d2.status <> d1.status)

Answer 3

You need to filter items with status "EXCLUDED" (as it is already doing) and also with nf other than "000002":

SELECT id, nf, status, data 
FROM documentos 
WHERE status LIKE ('%EXCLUIDO%') 
  and nf <> '000002'

edited after comment

In the case of the nf not valid, the query would need to be changed to use the NOT IN as below:

SELECT id, nf, status, data 
FROM documentos 
WHERE status LIKE ('%EXCLUIDO%') 
  nf NOT IN ('000002', '000003', '000003')

Answer 4

SELECT id, nf, status, data 
FROM documentos 
WHERE status = EXCLUIDO' 
  and nf not in ('000002')

Like your field status will always be "EXCLUDED", when it is not to be displayed, make an equal comparison, rather than a like in the where will make your search more performative.

In the restriction of nf you can go adding the nf according to your need, for example: nf not in ('000002','000003','000004'), but I believe that in time new ones will be added nf in this clause, with this it would be better to have some kind of field or even another table referencing these nf, not to leave them stays in your consultation.

Answer 5

If I understood correctly, I think with Except I could solve your problem:

create table #ttt1 (
    id int
    ,nf int
    ,status varchar(50)
    ,data datetime
)

insert into #ttt1 values
(1,1,'excluido',getdate()),
(2,2,'excluido',getdate()),
(3,2,'relacionado',getdate()+1)

select nf from #ttt1 t1 where status = 'excluido'
except
select nf from #ttt1 t1 where status = 'relacionado'

Will return only NF that has no related

Answer 6

Table used

SQL

SELECT id,nf,status,data, count(NF) as c FROM documentos group by NF having c = 1

Upshot

The same result is obtained with the query below

SELECT id,nf,status,data FROM documentos group by NF having count(NF) = 1

clause having

Answer 7

SELECT id,nf,status,data FROM documents WHERE STATUS ='EXCLUDED'