Left join using on keyword (Hibernate)

hi friends,

this is sql query

select tmrhdr.tmr_id,tmr_NAME,tmrlog.id,tmrlog.tmr_type from tmr_hdr tmrhdr
left join tmr_log tmrlog on tmrhdr.tmr_id = tmrlog.tmr_id and tmrlog.tmr_type = 'TimeSheet'
where tmr_user ='golden'

i want to write the same query in HQL. i try to write query.

select tmrhdr,tmrhdr.tmrId,tmrhdr.tmrName,tmrlog.id,tmrlog.tmrType from TmrHdr as tmrhdr
left join TmrLog as tmrlog where tmrhdr.tmrId = tmrlog.tmrHdr.tmrId and tmrlog.tmrType = 'TimeSheet'
where tmrhdr.user ='golden'

but result of HQL and sql query is different.

HQL query not take the all record of TmrHdr table . instead it will fire the query like inner join.

please help me.

how to right the left join in Hibernate.

It is doing inner join because you are using theta style inner joins in your where clause.

Remove

"tmrhdr.tmrId = tmrlog.tmrHdr.tmrId and "

from your Where clause.

Mark

hi friends,

according to your suggestion i am trying this solution

select tmrhdr,tmrhdr.tmrId,tmrhdr.tmrName,tmrlog.id,tmrlog.tmrType from TmrHdr as tmrhdr
left join TmrLog as tmrlog where tmrlog.tmrType = 'TimeSheet' and
tmrhdr.user ='golden'

but still i won't give the left join like my sql query.

could please help to solve this problem.

Thnaks,

OK try this

SELECT t
FROM TmrHdr t LEFT JOIN FETCH TrmLog log
WHERE t.tmrType='TimeSheet'
AND t.user='Golden'

That is 100% a Left join. If it doesn't work, then you might have to post your mappings for the two classes.

Mark

Hi Ganesh,

Use "WITH" keyword instead of "ON".

ON keyword is not supported by hibernate, in newer versions they may provide.

thanks