I use hibernate version 3.2.5.ga.
I am trying to use "not exists" in the named query like that:

<query name="GetNetworkUsersExceptTheseIds" cacheable="false">
<![CDATA[select u
from User as u
inner join u.userNetworks as un
inner join un.network as n
where n.id in (:nw_id)
and not exists(
select u1
from User u1
inner join userRoles as ur
inner join ur.role as rt
where u.id = u1.id
and rt.sort_order >= :role_sort_order)
order by u.lastName asc]]>
</query>

It gives me a parsing error ane tells me:
org.hibernate.hql.ast.QuerySyntaxException: Path expected for join!

I would appreciate if somebody hellps me to understand what's the problem.

Thank you
Natalie

Hi Natalie,
what is the database you are trying to use?
Can you post your DAO code? I think there is a minor possible syntax error in the HQL projection..

I would first test the DAO with this query:-
<query name="GetNetworkUsersExceptTheseIds" cacheable="false">
<![CDATA[
from User as u
inner join u.userNetworks as un
inner join un.network as n
where n.id in (:nw_id)
order by u.lastName asc]]>
</query>

and see if this works.And then extend as:-
<query name="GetNetworkUsersExceptTheseIds" cacheable="false">
<![CDATA[
from User as u
inner join u.userNetworks as un
inner join un.network as n
where n.id in (:nw_id)
and u.id not in(
select u1.id
from User u1
inner join userRoles as ur
inner join ur.role as rt
where u.id = u1.id
and rt.sort_order >= :role_sort_order)
order by u.lastName asc]]>
</query>

In both the cases you will get List<User> instances.

Hope this helps,
Latha.

Hi Latha,

Thank you very much for suggestions.

I did "not in" and did identify some syntax errors.
After "not in" run correctly I switch to "not exists".

Everything works fine now.

Thanks again, Natalie