I read the posts here, and tried the documentation, so I'm feeling really dense. I just can't figure out a left join. This is how I would do it in pure SQL:

select timeslot.queuesize, timeslotdelta.actualQueueSize
from timeslot,
linkdayplanlocation
left join timeslotdelta on
timeslotdelta.timestart = timeslot.timeslotstart and
timeslotdelta.linkid = linkdayplanlocation.id
where linkdayplanlocation.dayplanid = timeslot.dayplanid
and linkdayplanlocation.linkactivedate = '2004-04-10'
and timeslot.timeslotstart = '1970-01-01 06:00:00'
and linkdayplanlocation.locationId = 1


This is how I tried to do it in HSQL:
String HSQL_GET_LIST_BY_DAYPLAN_AND_TIME =
"select timeslot.queueSize, timeslotdelta.actualQueueSize " +
"from TimeSlotTable timeslot, " +
" LinkDayPlanLocationTable linkdayplanlocation " +
"left join TimeSlotDeltaTable timeslotdelta " +
" on timeslotdelta.timeSlotStart = timeslot.timeSlotStart" +
" and timeslotdelta.linkId = linkdayplanlocation.id " +
"where linkdayplanlocation.dayPlanId = timeslot.dayplanId" +
"and linkdayplanlocation.locationId = ? " +
"and linkdayplanlocation.linkActiveDate = ? " +
"and timeslot.timeSlotStart = ? ";

Query sql = session.createQuery(HSQL_GET_LIST_BY_DAYPLAN_AND_TIME);
sql.setInteger(0, location);
sql.setDate(1, date);
sql.setDate(2, time);
list = sql.list();


This returns the error in the subject: outer or full join must be followed by path expression....and is not valid HSQL - no "ON" keyword allowed.

But solving this is not as simple as removing the "ON". And the samples I've seen only have one criteria for the join.

Here are the .hbm.xml files. I don't mean to be a pain, or ask a dumb question, but I've really been unable to figure this out. Any help or hint would really be appreciated.

==========
<hibernate-mapping>
<class name="com.yourappt.beans.db.TimeSlotTable" table="timeslot">
<id name="id" column="id" type="int" unsaved-value="0">
<generator class="native">
<param name="sequence">timeslot_seq</param>
</generator>
</id>
<property name="dayplanId" column="DayplanId" type="int"/>
<property name="timeSlotStart" column="TimeSlotStart" type="java.util.Date"/>
<property name="queueSize" column="QueueSize" type="int"/>
<property name="status" column="DeletedFlag" type="int"/>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="com.yourappt.beans.db.TimeSlotDeltaTable" table="timeslotdelta">
<id name="id" column="id" type="int" unsaved-value="0">
<generator class="native">
<param name="sequence">timeslotdelta_seq</param>
</generator>
</id>
<property name="linkId" column="linkId" type="int"/>
<property name="timeSlotStart" column="timeStart" type="java.util.Date"/>
<property name="actualQueueSize" column="actualQueueSize" type="int"/>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="com.yourappt.beans.db.LinkDayPlanLocationTable" table="linkdayplanlocation">
<id name="id" column="Id" type="integer" unsaved-value="0">
<generator class="native">
<param name="sequence">linkdayplanlocation_seq</param>
</generator>
</id>
<property name="dayPlanId" column="DayplanId" type="int"/>
<property name="locationId" column="LocationId" type="int"/>
<property name="linkActiveDate" column="LinkActiveDate" type="java.util.Date"/>
</class>
</hibernate-mapping>

===========
Thanks again.
-Nathan