Help with rewriting a HQL query ( Hibernate Query Language )

Question

HQL noob here. How do I re-write this HQL query without using the exists clause. In SQL I can just join VisitorProfileField table and Visitor table and add the conditions of the exists using an AND.

But in plain HQL I am not able to get past some syntax violation I guess. Your help is much appreciated.

"select distinct v from Visitor v where v.id not in (select o.id from Operator o) " +
      " and exists (select vpf from VisitorProfileField vpf " +
      " where vpf.visitor = v and vpf.profileField.name = :subscription_field " +
      " and vpf.numberVal = :type and vpf.stringVal = :manual) "

Answer 1

I'm not sure whether I get the meaning of your query, but I guess something like:

select distinct vpf.visitor 
from VisitorProfileField vpf 
where vpf.profileField.name = :subscription_field 
      and vpf.numberVal = :type and vpf.stringVal = :manual
      and vpf.visitor.id not in (select o.id from Operator o)