How to filter record in a table with different condition

Question

How to find the record in a table which as only 'Return' as Reason?

I have table which has many records

As per the data in the table, it returns only records with policy number 5555, because only that policy number has only 'Return'; all other policy numbers have both 'Return' and 'Success' which I don't need.

Kindly help me.

Answer 1

SELECT *
FROM YOURTABLE
WHERE REASON = 'RETURN'
AND POLICYNUMBER NOT IN (
    SELECT POLICYNUMBER
    FROM YOURTABLE
    WHERE REASON = 'SUCCESS'
)