Hello.

I am using Hibernate 3 and have the following tables:

system
--------
id
name

module
--------
id
name

system_module
--------
id_system
id_module

I am trying to create a subquery that gives me a list of modules NOT assigned to a system, ie. that are IN the module table but NOT IN system_module table. Pretty straight forward SQL but I can't get it to work with Hibernate.

This is my subquery:
getHibernateTemplate().find("from module where id not in (select id from module, system_module where module.id = system_module.id_module"));

and I am getting a "column 'module' not mapped" error.

Below is the relevant excerpt from system-hbm.xml:

<bag
name="modules"
table="system_module"
lazy="true"
cascade="save-update"
>

<!-- @hibernate.collection-key tag is deprecated, use
@hibernate.key instead -->
<key
column="id_system"
>

</key>

<!-- @hibernate.collection-many-to-many tag is deprecated,
use @hibernate.many-to-many instead -->
<many-to-many
class="com.marcelovila.model.Module"
column="id_module"
outer-join="auto"
/>

</bag>

module-hbm.xml

This must be a pretty typical thing to do, and I've seen examples just like mine in various places. But mine won't work. I did find one document that subselects were not supported until mySql 5, but in the docs I have for mySql 4.1 it says they are supported.

Any clues?

Many thanks,
Bob

Assuming that there's a collection (bag, set, map, etc.) in Module back to System, HQL has already written that subquery for you:If there isn't such a collection then I think you'll need to use an SQL query.