title: JPA select entity and use subquery to assign value in entity all within a single query
body: <p>I'm having some trouble with a JPA query. I should tell you now, I'm not an expert in JPA at all.</p>
  
  <p>I have two classes, <code>Line</code> and <code>LineData</code>.</p>
  
  <p>The relationship between <code>Line</code> and <code>LineData</code> is that a Line can potentially have 100s of <code>LineData</code> objects associated to it. The reason I haven't explicitly joined them is because most of the time when I retrieve a <code>Line</code> entity, I don't need the <code>LineData</code> (and seeing as there could be 100s, it would be a waste to load them all and then not use them).</p>
  
  <p>However, what I would like to be able to do, when retrieving a <code>Line</code> entity, is get the maximum <code>LineData.value1</code> value as well and return that as a part of the <code>Line</code> entity, as <code>Line.maxVal1</code> . I've tried doing this with the following query:</p>
  
  <pre><code>SELECT l, l.maxVal1 = (SELECT MAX(d.value1) FROM LineData d WHERE d.name = l.name)
  FROM Line l
  WHERE l.name = example123
  ORDER BY l.name ASC
  </code></pre>
  
  <p>But <code>Line.maxVal1</code> is coming back as <code>null</code>. I have tried the subquery on its own and it does return an actual value. Is it possibly to do what I'm trying here? If so, how? </p>
  
  <p>Here are simplified versions of my classes:</p>
  
  <pre><code>public class Line {
      @Column(nullable = false, unique = true)
      public String name;
  
      @Column
      public boolean active;
  
      @Transient
      public Integer maxVal1;
  }
  
  public class LineData {
      @Column(nullable = false)
      public String name;
      @Column
      public Integer value1;
      @Column
      public Integer value2;
      @Column
      public Integer value3;
      ...
      @Column
      public Integer activeN;
  }
  </code></pre>
  
  <p><em>EDIT</em>
  Here's an example of the what the database values would look like:</p>
  
  <pre><code>Line
  name       | active
  ======================
  example123 | true
  example234 | false
  example345 | true
  
  LineData
  name       | value1 | value2 | ... | valueN
  ============================================
  example123 | 1      | 2      |     | 1
  example123 | 3      | 2      |     | 1
  example345 | 1      | 2      |     | 1
  example123 | 14     | 2      |     | 1
  example123 | 2      | 2      |     | 1
  example123 | 2      | 2      |     | 1
  example123 | 3      | 2      |     | 1
  </code></pre>
  
  <p>And the result of the query returning a Line object, with the values:</p>
  
  <pre><code>name       | active | maxVal1
  ==============================
  example123 | true   | 14
  </code></pre>