Left Outer Join Syntax

adcworks · **Joined:** Fri Jun 18, 2004 10:17 am **Posts:** 140

My ProductSchedule object contains a Set. With the following HQL I am getting no objects back from Hibernate because all my Sets are currently empty.

Code:

select s.id, s.title, s.status, s.lastUpdated, count(elements(s.updates))
from ProductSchedule as s group by s.id, s.title, s.status, s.lastUpdated
order by created asc

The HQL output reveals that Hibernate is using an INNER JOIN from ProductSchedules to ScheduleUpdates (the objects in the Set).

My brain is frying as to how I change the above HQL so that it does a left outer join.

I tried

Code:

select s.id, s.title, s.status, s.lastUpdated, count(elements(s.updates))
from ProductSchedule left outer join ScheduleUpdate as s group by s.id, s.title, s.status, s.lastUpdated
order by created asc

but I get

outer or full join must be followed by path expression [select s.id, s.title, s.status, s.lastUpdated, count(elements(s.updates)) from com.qas.newmedia.intranet.iq.dto.schedules.ProductSchedule as s left outer join ScheduleUpdate u group by s.id, s.title, s.status, s.lastUpdated order by title asc]

Cheers *sigh*

danny_mandel · **Posted:** Wed Jul 14, 2004 11:37 am

I think you need to say what property you are joining on. Here's an example query that uses outer joins (and inner joins) that works for me:

Code:

select distinct img from org.tolweb.treegrow.main.NodeImage as img join img.nodesSet as nodes left join nodes.synonyms as synonyms where nodes.name like ? or synonyms.name like ?

So in your case you would need to say what relation ScheduleUpdate has to ProductSchedule. In my case, I do an inner join on img's nodesSet property, then do an outer join on the synonyms property of the nodes.

Hope that helps.

shl · **Posted:** Thu Jul 15, 2004 7:40 am

There is an answer to all HQL related questions :)
HQL BNF http://www.hibernate.org/89.html

--
Leonid