Spring Find by unique properties

Given 1 relational table

Doctor_ID	Client_ID
2	2
2	3
2	4
3	5
4	2
4	3

And I want all the occurrences where client_id is not equals to 3, ignoring the same doctor_id.

In this example, I would only get the 4th row…

Doctor_ID	Client_ID
3	5

How could I achieve something like that?

Answer

This query with NOT EXISTS will give you the results needed.

SELECT * FROM myTable a
WHERE NOT EXISTS (
    SELECT 1 FROM myTable b WHERE a.Doctor_ID = b.Doctor_ID AND Client_ID = 3
)

The NOT EXISTS will filter out ant doctor id that has a client id equals to 3.