How to exclude all rows where a column value is different but other columns are the same

So many similar questions – mainly the questions are about how to select one of the duplicates where only a single column is different, but I want to exclude all of them from a query, and only get the ones where a particular field isn’t different.

I am looking for all the reference_no where the status is -1, except for those where the status is both -1 and 1 for the same reference_no, as in the table below. The query should return only row Id 4. How do I do that?

This is using SQL server 2016

| id  | process_date | status | reference_no |
| --- | ------------ | ------ | -----------  |
| 1   | 12/5/22      | 1      | 789456       |
| 2   | 12/5/22      | -1     | 789456       |
| 3   | 12/5/22      | 1      | 789456       |
| 4   | 12/5/22      | 1      | 321654       |

>Solution :

If I understand correctly you want a not exists check

select *
from t
where status = -1 
and not exists (
  select * from t t2 
  where t2.status = 1 and t2.reference_no = t.reference_no
);