title: JPA/JPQL JOIN Subselect/Subquery
body: <p>I have trouble to translate some easy SQL statement to JPQL, because of a used subquery, which is not supported by JPQL.</p>
  
  <p>Can someone give me a hint, how to achieve the same result with JPQL or JPA2 Criteria API?</p>
  
  <p>Given (Simplified fake data to demonstrate the problem):</p>
  
  <pre><code>CREATE TABLE person (id integer, name text);
  CREATE TABLE phone (id integer, person_id integer, type text, number text);
  
  INSERT INTO person VALUES (1, "John");
  INSERT INTO person VALUES (2, "Mike");
  INSERT INTO person VALUES (3, "Paul");
  INSERT INTO person VALUES (4, "Walter");
  
  INSERT INTO phone VALUES (1, 1, "MOBILE", "+49-123-11111");
  INSERT INTO phone VALUES (2, 1, "HOME"  , "+49-123-22222");
  INSERT INTO phone VALUES (3, 2, "WORK"  , "+49-123-33333");
  INSERT INTO phone VALUES (4, 4, "MOBILE", "+49-123-44444");
  
  -- Select all from person and their mobile number if possible
  -- This query has to be translated to JPQL
  
  SELECT person.name, mobile.number FROM person LEFT JOIN (
    SELECT * FROM phone WHERE type = "MOBILE"
  ) AS mobile ON person.id = mobile.person_id;
  </code></pre>
  
  <p>Expected result:</p>
  
  <pre><code>| name   | number        |
  |--------|---------------|
  | John   | +49-123-11111 |
  | Mike   |               |
  | Paul   |               |
  | Walter | +49-123-44444 |
  </code></pre>
  
  <p>Java:</p>
  
  <pre><code>class Person {
      String name;
      List&lt;Phone&gt; phones;
  }
  
  class Phone {
      String type;
      String number;
  }
  </code></pre>
  
  <p>JPQL (not working as expected :-( ):</p>
  
  <pre><code>SELECT person.name, phone.number FROM Person person
      LEFT JOIN person.phones AS phone
      WHERE phone.type = "MOBILE"
  </code></pre>