I have a one to many relation between table A and table B, and I need to get the row with latest date <= now() from table B from within a LEFT JOIN's subquery. The hql query that I am using is returning all the dates and ignore my subquery in the WHERE clause, but when i translate the same query into sql and run it returns the correct result. Could someone help .. I tried everything I could think of....

Here are the Mappings:

<class name="Store" table="merchants" schema="public">
<id name="id" type="int" column="merchant_id">
<generator class="sequence">
<param name="sequence">merchants_seq</param>
</generator>
</id>
<set name="currRebate" table="merchant_rebates" schema="rebate" lazy="false" cascade="none" inverse="true">
<key column="mr_merchant_id"/>
<one-to-many class="MerchantRebate"/>
</set>
</class>

<class name="MerchantRebate" table="merchant_rebates" schema="rebate">
<id name="id" type="int" column="mr_id">
<generator class="sequence">
<param name="sequence">merchant_rebates_seq</param>
</generator>
</id>
<property name="merchantId" column="mr_merchant_id" type="int"/>
<property name="startDate" column="mr_start_date" type="timestamp"/>
</class>

And here is the query I came up with:

<query name="getStoreById">
<![CDATA[
SELECT p FROM Store as p LEFT JOIN p.currRebate AS curRebate
WHERE p.id = id AND curRebate.startDate = (SELECT MAX(mr.startDate) FROM MerchantRebate AS mr
WHERE mr.startDate <= now())
]]>
</query>