How to filter record in a table with different condition

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

I have table which has many records

This is Table Data

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.

Asked on September 1, 2020 in Sql.
Add Comment
2 Answer(s)
SELECT * FROM YOURTABLE WHERE REASON = 'RETURN' AND POLICYNUMBER NOT IN (     SELECT POLICYNUMBER     FROM YOURTABLE     WHERE REASON = 'SUCCESS' ) 
Answered on September 1, 2020.
Add Comment

I would use not exists:

select t.* from mytable t where not exists (     select 1      from mytable t1      where t1.policynumber = t.policynumber and t1.reason <> 'Return' ) 

This query would take advantage of an index on (policynumber, reason).

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.