select
*
from
t1 left join t2
on
(t1.a = t2.a and t1.b=t2.b)
or
select
*
from
t1 left join t2
on
t1.a = t2.a and t1.b=t2.b
[without parenthes]
t1 has one-to-many relationship to t2 on column a (so have a "set" mapping from t1 to t2)
t1 has many-to-many relationship to t2 on column b (no mapping so far)
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
How can the above SQL query be written in HQL?
I checked for "with" clause in HQL.However it is expecting a constant on the right hand side,not a column from a table.
Inorder to join with another table in HQL you need to have mapping to that table.
My Question is what is the neat way or the only way to achieve the above SQL in HQL?
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)?
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?)