Hi,
I have two tables tbl_object_relations and tbl_objects. It is the B tree relation - each object can be related witch other objects. So tbl_object_relations is related with tbl_objects twice by object_relation_id.object_id and object_relation_id.related_object_id. When I would like to find which objects are related witch object_id I do join SQL query (see bellow). Query is working properly and I tested it in SQL Server - return correct results.
When I execute my query in Hibernate I am getting list of tables with two classes result[0] - TblObjectRelations.class and result[1] TblObjects.class. The Result[0] is correct but unfortunatelly result[1] TblObjects.class is always returning TblObjects related with object_relation_id.object_id. (not TblObjects related with object_relation_id.related_object_id).

Could you explain me where is my issue or show me how to do this in one query.

Regards
Edward

Table - tbl_object_relations
object_relation_id
(K) object_id
(K) related_object_id
(K) object_zone_id
...

Table - tbl_objects
(K) object_id
object_name
...

Query:
result = session.createSQLQuery("SELECT * FROM tbl_object_relations INNER JOIN tbl_objects ON " +
"tbl_object_relations.related_object_id = tbl_objects.object_id " +
"WHERE (tbl_object_relations.object_id = :objectId) " +
"ORDER BY tbl_object_relations.sequence")
.addEntity(TblObjectRelations.class)
.addEntity(TblObjects.class)
.setParameter("objectId", objectId)
.list();

Loop
for (Iterator it = result.iterator(); it.hasNext();) {
Object resultRow[] = (Object[]) it.next();
TblObjectRelations tblObjectRelation = (TblObjectRelations) resultRow[0];
TblObjects tblObjectRelation = (TblObjects) resultRow[1];
}



<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 07-Apr-2009 14:56:41 by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
<class catalog="prototype"
name="com.media.concrete.connect.db_tables.TblObjectRelations" schema="dbo" table="tbl_object_relations">
<composite-id class="com.media.concrete.connect.db_tables.TblObjectRelationsId" mapped="false"
name="id" unsaved-value="undefined">
<key-property name="objectId" type="long">
<column name="object_id"/>
</key-property>
<key-property name="relatedObjectId" type="long">
<column name="related_object_id"/>
</key-property>
<key-property name="objectZoneId" type="int">
<column name="object_zone_id"/>
</key-property>
</composite-id>
<many-to-one class="com.media.concrete.connect.db_tables.TblObjectZones" fetch="select"
insert="false" name="tblObjectZones" update="false">
<column name="object_zone_id" not-null="true"/>
</many-to-one>
<many-to-one class="com.media.concrete.connect.db_tables.TblObjects" fetch="select"
insert="false" name="tblObjectsByRelatedObjectId" update="false">
<column name="related_object_id" not-null="true"/>
</many-to-one>
<many-to-one class="com.media.concrete.connect.db_tables.TblObjects" fetch="select"
insert="false" name="tblObjectsByObjectId" update="false">
<column name="object_id" not-null="true"/>
</many-to-one>
<property generated="never" lazy="false" name="objectRelationId" type="long">
<column name="object_relation_id" not-null="true"/>
</property>
<property generated="never" lazy="false" name="sequence" type="int">
<column name="sequence" not-null="true"/>
</property>
</class>
</hibernate-mapping>



public class TblObjectRelations implements java.io.Serializable {
private TblObjectRelationsId id;
private TblObjectZones tblObjectZones;
private TblObjects tblObjectsByRelatedObjectId;
private TblObjects tblObjectsByObjectId;
private long objectRelationId;
private int sequence;


public class TblObjects implements java.io.Serializable {
private long objectId;
private TblObjects tblObjects;
private int objectTypeId;
private int objectSubtypeId;
private String objectName;
private Set tblObjectses = new HashSet(0);