Hibernate version: 3.x
Name and version of the database you are using: db2 8.x

I am trying to work with legacy Databases where there are two simple tables with no primary keys and no foriegn keys but there is a common column.

Table EMPLOYEE - Columns ->EMPNUM, FIRSTNAME, LASTNAME
Table EMP_PHONE - Columns ->EMPLOYEE_ID, OFFICE_PHONE

In these two tables EMPNUM/EMPLYEE_ID in Unique(both are related by these columns) but not declared as PK. In such cases how do I write mapping files for both of these tables. I tried like this:

EmpPhone.hbm.xml:
********
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="com.sf.hibernate.EmpPhone" table="EMP_PHONE">
<id name="employeeID" column="EMPLOYEE_ID" type="string"></id>
<property name="officePhone" column="OFFICE_PHONE" type="java.lang.String" />

</class>
</hibernate-mapping>
********
Employee.hbm.xml:
*********
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="com.sf.hibernate.Employee" table="EMPLOYEE">
<id name="empNo" column="EMPNO" type="string">
</id>
<property name="firstName" column="FIRSTNME" type="java.lang.String" />
<property name="lastName" column="LASTNAME" type="java.lang.String" />
<one-to-one name="empPhone" class="com.sf.hibernate.EmpPhone" />
</class>
</hibernate-mapping>
*******************

I tried with this code with two classes for both table:

s.createQuery("from Employee as emp left join emp.empPhone as phone where emp.empNo=phone.employeeID").list();

I even tried with this:

s.createQuery("from Employee as emp left join EMmpPhone as phone where emp.empNo=phone.employeeID").list();

Not working in both cases. Help me with a solution for such cases. Please note that I am not seeing any problem with NATIVESQL.

Made few changes to the code while mapping the objects and it started working. Issue was related to mapping the results and there is no issue with mapping itself. Not sure if this is the right approach. I am assuming that this may be the approach for such cases. Is there any better solution for such cases (where there are no primary keys or foreign keys)?

Thanks

HI..
is it possible for you to log on messenger so that v can discuss abt this.
Thanks