I am not sure how to do a left outer join in a HQL subquery
Code:
from ParentObj parentObj left join parentObj.childCollection as children
where children.anotherParentObj.anotherParentField != 1
generates a SQL
Code:
SELECT parent.parent_id, childTable.parent_id
FROM ParentTable parent, ChildTable childTable, AnotherParent anotherParent
WHERE parent.parent_id = childTable.parent_id(+)
AND anotherParent.anotherParent_id != 1
whereas I am actually interested in this one... where I filter my child based on another parent and then do a left join on this child with the base parent!
Code:
SELECT parent.parent_id, childAlias.parent_id
FROM ParentTable parent,
(SELECT *
FROM ChildTable childTable, AnotherParent anotherParent
WHERE childTable.anotherParent_id = anotherParent.anotherParent_id
AND anotherParent.anotherParent_id != 1) childAlias
WHERE parent.parent_id = childAlias.parent_id(+)
Another question would be how they would do a criteria query for this!
Thanks
Krishna