I'm trying to perform an inner join in a mapping file for the SNMP_TRAP_LOG table to provide the resolved machine name from MACHINES table. There is no FK between the tables but the mach_id in SNMP_TRAP_LOG is the machine PK in the MACHINES table.

In SQL i would use:

SELECT [id] as [id],
[writetime] as [writetime],
[mach_id] as [mach_id],
[port_id] as [port_id],
[timeslot] as [timeslot],
[trap_oid] as [trap],
[severity] as [severity],
[message] as [message],
[source_app] as [source],
[open] as [open],
[name] as [name] FROM snmp_trap_log
inner join machines on machines.[machine] = snmp_trap_log.[mach_id]

In hibernate my current mapping creates the equivalent of this:

SELECT [id] as [id],
[writetime] as [writetime],
[mach_id] as [mach_id],
[port_id] as [port_id],
[timeslot] as [timeslot],
[trap_oid] as [trap],
[severity] as [severity],
[message] as [message],
[source_app] as [source],
[open] as [open],
[name] as [name] FROM snmp_trap_log
inner join machines on snmp_trap_log.[id] = machines.[machine]

Hibernate version:

2.0.0.1001

Mapping documents:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Digitalk.Gateway.Core.DomainObjects.SnmpTrapLog,Digitalk.Gateway.Core" table="snmp_trap_log" lazy="true">
<id name="ID" column="id" type="int">
<generator class="native" />
</id>
<property name="Writetime" column="writetime" type="DateTime" not-null="true" />
<property name="MachId" column="mach_id" type="int" not-null="true" />
<property name="PortId" column="port_id" type="int" />
<property name="Timeslot" column="timeslot" type="byte" />
<property name="TrapOid" column="trap_oid" type="string" not-null="true" />
<property name="Severity" column="severity" type="byte" not-null="true" />
<property name="Message" column="message" type="string" not-null="true" />
<property name="SourceApp" column="source_app" type="byte" not-null="true" />
<property name="Open" column="`open`" type="Boolean" not-null="true" />
<join table="Machines" >
<key column="machine" />
<property name="MachineName" column="name" type="string"/>
</join>
</class>
</hibernate-mapping>

Name and version of the database you are using:
SQL Server 2000 - gateway

Any help would be greatly appreciated.

Paul.