title: HQL outer join on multiple columns (joining 2 tables with multiple conditions)
body: <pre><code>select 
  * 
  from
  t1 left join t2
  on 
  (t1.a = t2.a and t1.b=t2.b)
  </code></pre>
  
  <p>or</p>
  
  <pre><code>select 
  * 
  from
  t1 left join t2
  on 
  t1.a = t2.a and t1.b=t2.b 
  [without parenthes]
  </code></pre>
  
  <p>t1 has one-to-many relationship to t2 on column a (so have a "set" mapping from t1 to t2)</p>
  
  <p>t1 has many-to-many relationship to t2 on column b (no mapping so far)</p>
  
  <pre><code>case 1 : 
  b in t1 null, b in t2 null
  
  
  case 2 : 
  b in t1 not null, b in t2 not null 
  
  
  case 3 : 
  b in t1 not null, b in t2 null 
  
  case 4 : 
  b in t1 null, b in t2 not null 
  </code></pre>
  
  <p>How can the above SQL query be written in HQL? </p>
  
  <p>I checked for "with" clause in HQL.However it is expecting a constant on the right hand side,not a column from a table.</p>
  
  <p>Inorder to join with another table in HQL you need to have mapping to that table.</p>
  
  <p>My Question is what is the neat way or the only way to achieve the above SQL in HQL?</p>
  
  <p>Do I have to create separate mapping for each of those columns from t1 to t2 and join separately with those mappings (i.e t2 to be joined twice for col a once and col b once)?</p>
  
  <p>Also,I would like to know if all the above cases are valid cases for the above left join query.(Say col b is null in both tables, can 2 tables be joined with "null" values?)</p>