Hi,

I'm trying to use <load-collection> to configure a set of objects AnnModule in my PrxEntry object. These 2 tables are distant, from the DB schema point of view and to link a module to an entry we need to go through another table called PrxBiogroup that modelize a tree structure.
See below the DB schema and the code I added to my PrxEntry mapping in order to configure my set.
The sql query generated by hibernate is correct and works, but my set of AnnModule still empty when I retrieve a PrxEntry from my DB that Ii know it's linked to 2 modules.
Do you know if <load-collection> works in case of distant tables to populate set as I'm doing ?
Or do you see anything wrong in my code ?

Hibernate version: 3.2

DB schema:see schema from prx_entry table to ann_module table

Mapping documents:

<set name="annModules" inverse="true" cascade="none" lazy="true" >
<key column="ANN_MODULE_ID" />
<one-to-many class="com.cbd.tpdb.model.database.ann.AnnModule" />
<loader query-ref="loadModules" />
</set>

<sql-query name="loadModules">
<load-collection alias="m" role="com.cbd.tpdb.model.database.nws.PrxEntry.annModules" />
SELECT distinct {m.*}
FROM prx_entry_biogroup eb, prx_biogroup bg, ann_module m,
(
select mb.ann_module_id moduleId, bg.tree_id treeId
from prx_biogroup bg, ann_module_biogroup mb
where bg.prx_biogroup_id = mb.prx_biogroup_id
and bg.biogroup_class='TREE'
) moduleBiogroup
WHERE eb.prx_biogroup_id = bg.prx_biogroup_id
and bg.tree_id=moduleBiogroup.treeId
and m.ann_module_id = moduleBiogroup.moduleId
and eb.prx_entry_id = ?
</sql-query>

POJOs:
My POJO PrxEntry contains a set on AnnModule as following : Set<AnnModule> annModules

Name and version of the database you are using: Oracle 10g

The generated SQL (show_sql=true):

SELECT m.ann_module_id AS ann1_0__, m.ann_module_id AS ann1_18_0_,
m.VERSION AS version18_0_, m.module_name AS module3_18_0_,
m.description AS descript4_18_0_, m.userstamp AS userstamp18_0_,
m.TIMESTAMP AS timestamp18_0_, m.editorial_status AS editorial7_18_0_,
m.created_by AS created8_18_0_, m.created_date AS created9_18_0_,
m.last_updated_by AS last10_18_0_, m.last_updated_date AS last11_18_0_,
m.published_by AS published12_18_0_,
m.published_date AS published13_18_0_,
m.published_version AS published14_18_0_
FROM prx_entry_biogroup eb,
prx_biogroup bg,
(SELECT mb.ann_module_id moduleid, bg.tree_id treeid
FROM prx_biogroup bg, ann_module_biogroup mb
WHERE bg.prx_biogroup_id = mb.prx_biogroup_id
AND bg.biogroup_class = 'TREE') modulebiogroup,
ann_module m
WHERE eb.prx_biogroup_id = bg.prx_biogroup_id
AND bg.tree_id = modulebiogroup.treeid
AND m.ann_module_id = modulebiogroup.moduleid
AND eb.prx_entry_id = ?