I have a Hibernate class like this:
public class Resource {

public Set getUsers() {
...
}

}

I want to get all Resources that have no users. I tried two method, but both failed with MySQL syntax error

(1) HQL: "from Resource where users.size=0"
Translated SQL sth like: "select * from Resource where (select count(*) from ResourceUsers where Resource.id=ResourceUsers.resource_id)=0"
Error near the 2nd select

(2) HQL: "from Resource where not exists elements(Resource.users)"
Translated SQL sth like: "select * from Resource where not exists(select ResourceUsers.resource_id from ResourceUsers where Resource.id=ResourceUsers.resource_id)"
Error near exists

I tested manually, both SQL don't work with MySQL 4.1. I wonder if this is a bug or there is anything to work around the mysql limit?

Thanks,

- Howard

How does the generated SQL look like, and what is mysql complaining about?

I had the SQL in the original post:

(1) Translated SQL sth like: "select * from Resource where (select count(*) from ResourceUsers where Resource.id=ResourceUsers.resource_id)=0"
MySQL complains: syntax error near "select count(*) from ResourceUsers where..."

(2) Translated SQL sth like: "select * from Resource where not exists(select ResourceUsers.resource_id from ResourceUsers where Resource.id=ResourceUsers.resource_id)"

MySQL complains: syntax error near "exists(select ResourceUsers.resource_id ..."

Are you sure you are using a mysql version which supports subselects? Can you get the SQL to work by modifying it manually?

Please read the documentation fully, regarding the use of size() and subselects.

My mistake... I was using MySQL 4.0.

Just tried MySQL 4.1, the subselect works (by manual query to DB), but the JDBC driver is not working :(

Thanks all for your help.