CODEWITHSUNDEEP
javamysqlsqlhibernatespring-data-jpa
Tiago Vieira
Tiago Vieira

Reputation: 65

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?

Upvotes: 1

Views: 66

Answers (1)

isaace
isaace

Reputation: 3429

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.

Upvotes: 2

Related Questions