SQL: finding duplicates that do not have a success message

I am struggling to return all batch_id’s that do not contain a corresponding “PASS”

+--------------------------------------+
| ID         Batch_ID          STATUS  |
+--------------------------------------+
| 1          100               PENDING |
| 2          100               PENDING |
| 3          100               PASS    | < 100 has passed
| 4          101               PENDING |
| 5          101               PASS    | < 101 has passed
| 6          103               PENDING |
| 7          103               PASS    | < 103 has passed
| 8          104               PENDING |
| 9          104               PENDING |
| 10         104               PENDING | < i want to return this because 104 does not contain a "PASS"
| 11         105               PENDING |
| 12         105               PASS    | < 105 has passed
| etc..                                |
+--------------------------------------+

From the above table, i need an sql statement that only returns batch_id 104 because it never had a status of “PASS”

The database is an oracle DB, therefore if its is not possible with a simple select statement a PL/SQL could be used. Does anyone have any suggestions?

Answer

You can select all batches that are pending, and among those filter out the one’s that have once passed. One solution could be:

select id, batch_id, status from table t
where t.status = 'PENDING'
and NOT EXISTS (select 'X' from table t2 where t.batch_id = t2.batch_id and t2.status = 'PASS');