I have the following tables:

Market
-------------------
Market_ID
MarketAbbrev
<Other fields>

DownLog
-------------------
DownLog_ID
Market_ID
BroadcastDate
<Other fields>

There is a one-to-many relationship from market to downlog.

Here is the SQL query I am trying to Hibernate:

select m.*
from market as m
left join downlog as d
on m.market_id = d.market_id
and d.broadcastDate = ?
where d.market_id is null;

What this query does is return the markets that have no downlog records for a given day. Here is what I have so far:

select m
from Market as m
left join m.downLogs as d

How do I add the clause "and d.broadcastDate = ?" to the Hibernate query so that it only returns Market records that do not have DownLog records for a given date?

Frank

In the where clause.
But wo mapping can't help more

Here are the abbreviated HBM files:
------------------------------------------------------
<hibernate-mapping>
<class name="Market" table="market">
<id name="id" column="Market_ID" type="long">
<generator class="native"/>
</id>
<property name="marketAbbrev" column="marketAbbrev" " type="string"/>
<set name="downLogs">
<key column="Market_ID" />
<one-to-many class="DownLog"/>
</set>
</class>
</hibernate-mapping>
--------------------------------------------------------------------------
<hibernate-mapping>
<class name="DownLog" table="downlog">
<id name="id" column="DownLog_ID" type="long">
<generator class="native"/>
</id>
<property name="broadcastDate" column="BroadcastDate" type="date"/>
<property name="processed" column="Processed" type="char"/>
<many-to-one name="markets" class="Market" column="Market_ID"/>
</hibernate-mapping>
-------------------------------------------------------------------------
I don't believe you can do it in the where clause because if you look at the "on" clause:

on m.market_id = d.market_id
and d.broadcastDate = ?

it joins with Market only those records from DownLog for that particular date. Now any joined records that have a field from DownLog that are null are the Markets that do not have a DownLog record for that date. I don't believe you can put broadcastdate in the "where" clause because we are looking for missing DownLog records, and that's where broadcastDate is stored.

If you know another way of doing it, I would appreciate it.
----------------------------
Frank

This is a planned feature for Hibernate 2.2.