title: HQL Left join with condition refering to third entity
body: <p>I have three entities/tables</p>
  
  <p>A has a one to many relation to B and B has a one to one relation to C.</p>
  
  <p>I want to get all A and left join with B that have a specific C value</p>
  
  <p>e.g.</p>
  
  <p>I want A with B that have C.value = 'A'</p>
  
  <p>Table A</p>
  
  <pre><code>  id
  
     1
  
     2
  
     3
  </code></pre>
  
  <p>Table B</p>
  
  <pre><code>idToA ║ idToC ║ value
  
      1 ║     1 ║   'X'
  
      2 ║     1 ║   'Y'
  
      3 ║     2 ║   'Z'
  </code></pre>
  
  <p>Table C</p>
  
  <pre><code>  id ║  value
  
     1 ║    'A'
  
     2 ║    'B'
  
     3 ║    'C'
  </code></pre>
  
  <p>and have the result be</p>
  
  <pre><code>A.id ║ b.value
  
     1 ║     'X'
  
     2 ║     'Y'
  
     3 ║     
  </code></pre>
  
  <p>I managed to get the following SQL:</p>
  
  <pre><code>select a.id, b.value 
  from A left outer join B on A.id = B.idToA and B.idToC in 
    (select id from C where C.value = 'A')
  </code></pre>
  
  <p>But I can't seem to find a way to convert this query to HQL because of the subquery inside the condition for the left join.</p>