java - Select records which doesn't have specific records in joined table

Question

Hi I'm trying to select records from one table which doesn't have records in connected many-to-many table with specific values.

I will explain on sample tables:

documentation:
id_documentation
irrelevant_data

user:
id_user
irrelevant_data


documentation_user:
id_documentation
id_user
role

What I want to achieve is to select every single documentation which doesn't have user in specific role. Any ideas?

The main problem is that I'm using java's CriteriaBuilder to create query so using subqueries is impossible (I think).

Answer 1

You can add restrictions on your left join using: createAlias(java.lang.String, java.lang.String, int, org.hibernate.criterion.Criterion) method, see API.

Check this answer for an example on how to use the left join with a criteria.