I have two entities, a Company and an Address. The Company has two addresses, the mainAdrress that is required and the postalAddress that is optional (both configured as OneToOne). Now I need a list with all my companies and their city names for both addresses. I use this query:
SELECT
c.displayName,
c.mainAddress.city,
c.postalAddress.city
FROM
Company c
Hibernate is translating this query to SQL:
select
companyent0_.displayName as col_0_0_,
addressent1_.city as col_1_0_,
addressent2_.city as col_2_0_
from
Company companyent0_,
Address addressent1_,
Address addressent2_
where
companyent0_.mainAddress_primaryKey=addressent1_.primaryKey
and companyent0_.postalAddress_primaryKey=addressent2_.primaryKey
The effect is, that only those companies having both addresses set will be returned. Looking at the SQL statement I understand why. Nevertheless, how can I create a query that will return NULL for the second city name if the company does not have such an address? Can this be done using a special query syntax or do I have to configure the relations between the entities in another way?
Thanks to anyone that can help.
