Spring Find by unique properties

user_13946707 0
Viewed 51

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?

spring-data-jpa hibernate mysql sql java
1 Answers

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.

user_57309490
Related