title: Which join operation to use to combine several tables with JPA/SQL query?
body: <p>I have a JPA database setup like the following:</p>
  
  <pre><code>@Entity
  public class Contact {
  
      @Id
      private Long id;
      @Column(length = 64)
      private String firstname;
      @Column(length = 64)
      private String surname;
  
      @OneToMany(mappedBy = "contact") 
      private List&lt;Address&gt; adresses = new ArrayList&lt;Address&gt;();
      @OneToMany(mappedBy = "contact") 
      private List&lt;Telephone&gt; telephones = new ArrayList&lt;Telephone&gt;();
  
  }
  
  @Entity
  public class Address {
  
      @Id
      private Long id;
      @Column(length = 128)
      private String street;
      @Column(length = 16)
      private String plz;
  
  
      @ManyToOne 
      private Contact contact;
  
  }
  
  @Entity
  public class Telephone {
  
      @Id
      private Long id;
      @Column(length = 32)
      private String number;
  
  
      @ManyToOne
      private Contact contact;
  
  }
  </code></pre>
  
  <p>Now I have the following query for my search form:</p>
  
  <pre><code>em.createQuery("SELECT DISTINCT c FROM Contact c LEFT JOIN c.addresses a LEFT JOIN c.telephones t "
                          + "WHERE c.surnameLIKE '%"+var_surname+"%' "
                          + "AND c.firstname LIKE '%"+var_firstname+"%' "
                          + "AND a.street LIKE '%"+var_street+"%'"
                          + "AND t.nummer LIKE '%"+var_telephone+"%'"
                          ).getResultList();
  </code></pre>
  
  <p>My question is now how can I join all 3 tables in a single JPA-query to look up for the firstname, street and number for example? I tried already with LEFT JOIN, but I don't get any results when for example the Telephone table is empty (it properly works if all tables have appropriate entries). I would like to have a result list with the Contacts even when there are no phone numbers and just one result even if a contact has more than one phone number for example.</p>
  
  <p>Many thanks for your help in advance.</p>