0 Replies Latest reply on Sep 17, 2007 8:33 AM by f.ulbricht

    EJBQL Query Question

    f.ulbricht Newbie

      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.