[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - EJBQL Query Question

[f.ulbricht@qualitype.de]

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.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4085019#4085019

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4085019