Hello!
I have the following 2 Entities (shortened)
Code:
public class User {
@Id @GeneratedValue
protected long id;
}
Code:
public class Lieferant {
@Id @GeneratedValue
protected long id;
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(
joinColumns=@JoinColumn(name="lieferantid", referencedColumnName="id"),
inverseJoinColumns=@JoinColumn(name="userid", referencedColumnName="id")
)
private Set<User> users = new HashSet<User>();
}
now i want to figure out,
which User are NOT in any Lieferant.users.
The corresponding SQL sould be like this:
SELECT * FROM Users WHERE id NOT IN (SELECT userid FROM Lieferanten_Users)so far my closest approach to get there was this:
Code:
SELECT u FROM User u WHERE u NOT IN (SELECT l.users FROM Lieferant l)
---- generated (not working) SQL:
select
user0_.id as id130_
from
dbo.Users user0_
where
user0_.id not in (
select
. // here is the error: just a dot instead of user3_.id
from
dbo.Lieferanten lieferant1_,
dbo.Lieferanten_Users users2_,
dbo.Users user3_
where
lieferant1_.id=users2_.lieferantid
and users2_.userid=user3_.id
)
also not working:
Code:
SELECT u FROM User u WHERE u.id NOT IN (SELECT l.users.id FROM Lieferant l)
> illegal attempt to dereference collection [lieferant1_.id.users] with element property reference [id]
why "lieferant1_.id.users" and not "lieferant1_.users.id"?
how to do it right?
Chris
Login
FAQ
Search