How to write a query to find a record which is not processed

Question

I am trying to write a query from a table A which has 2 columns :

ID , STATUS

The Status can be PROCESSING, NOTPROCESSED, FAILED, SUCCESS

When a record is successfully processed, a new record is created in the DB with STATUS as PROCESSED and the ID is the same as the previous NOTPROCESSED record.

The Sample Records in DB would like :

1      NOTPROCESSED
2      PROCESSED
1      PROCESSED
3      NOTPROCESSED
4      NOTPROCESSED
2      PROCESSED
3      NOTPROCESSED
4      NOTPROCESSED

The records can appear as duplicate for NOTPROCESSED.

I have to query the records which are NOTPROCESSED i.e

3      NOTPROCESSED
4      NOTPROCESSED

Its getting quite confusing to write the query.

Can anyone help with the logic.

Answer 1

you may use not exists to get this output.

select distinct a.id,a.status
  from table a
 where a.status='NOTPROCESSED'
   and not exists (select null
                     from table b
                    where b.id=a.id
                      and b.status='PROCESSED')