Hibernate version: 3.2.6.ga

Hi,
my problem is to write a HQL-query, which finally translates into the following SQL-query:


The HQL I have (which gets not the correct result!) is:


Hibernate show me an error, if I add a bracket:


The relation between Person and Contact is 1..*
Has somebody an idea, how it could work?
I have testet many different HQLs, but nothing works.
It seems, that Hibernate does not support brackets in joins in any form.

As far as I known, "joins" in HSQL is somethink another than you expected. What about such HSQL:

Thanks for reply, but the Problem is, that not all persons have a contact and your join lists only persons, who have one.
That is the reason, why I have to use an left join.
Futhermore, the condition cv.type='email' has to be a join-condition.
It won't work, if it is within the WHERE-part.

Hm.. may be this will select correclty:


But I am not sure, in common I tried also using of Hibernate joins, but got lot of problems, that's why I suggested just to use capabilities of WHERE.

I tried to put the condition into the Where-part, but that is not the same: Not all persons are selected, because the contact.id can exist and the cv.type can be something else than 'email'.
In this case, I still want to select the person but with an empty e-mail (NULL).

You have to create a "one to many" join from Person to contact using the person.id

and then use the following :

select p.name as name, cv.mail as mail
from person p left join p.contacts c
where (c.person_id is null) or (c.person_id is not null and c.cv.type = 'email')
(Assume that you have a many-to-one property from contact to contactvalue, actually your condition cv.type='email' shouldn't exists in the left join condition but in a where clause)