We are using Hibernate version 3.2.0.ga with Hibernate Annotations 3.2.1.ga with
The database I'm using is Oracle9i.

I have three database tables: CUST_HISTORY, CUST and ADDRESS

(a) CUST_HISTORY and CUST does *not* have FK realtionship
(relationship is not possible as CUST hold only active customers
where as CUST_HISTORY can contain active and inactive customers)
(b) CUST and ADDRESS have 1-many realtionship

Simplified table definitions are as below:
============================================
CUST_HISTORY(ID (primary key), CUST_NUMBER...other fields)
CUST(CUST_NUMBER (pk), ...other fields)
CUST_ADDRESS(ID (primary key), CUST_NUMBER (Foreign Key), other fields

Domain object definitions are as below:
============================================
------------ CUST_HISTORY ------------
@Entity
@Table(name = "CUST_HISTORY")
public class CustHistory {
@Id
private int Id;
private String custNumber;
//Other fields, and Getters and Setters
}

------------ CUST ------------
@Entity
@Table(name = "CUST")
public class Cust {
@Id
@Column(name = "CUST_NUMBER")
private int Id;
//Other fields, and Getters and Setters
private Set<CustAddress> custAddresses = new HashSet<CustAddress>(0);
}

------------ CUST_ADDRESS ------------
@Entity
@Table(name = "CUST_ADDRESS")
public class CustAddress {
@Id
private int Id;
Cust cust;
//Other fields, and Getters and Setters
}

***HAVING SAID THAT THERE IS NO FOREIGN KEY RELATIONSHIP BETWEEN CUST_HISTORY and CUST***
How do we go about forming a HQL to get data from "CustHistory" and "Cust" domain objects.
That is to get:
============ HQL I NEED============
select ch.Id, ch.custNumber,c.custNumber, ca.custNumber
from CustHistory ch inner join Cust c
inner join c.CustAddresses ca

Equivalent SQL is as below:
============ SQL EQUIVALENT ============
SELECT CH.ID, CH.CUST_NUMBER,C.CUST_NUMBER, CA.CUST_NUMBER
FROM CUST_HISTORY CH
,CUST C
,CUST_ADDRESS CA
WHERE CH.CUST_NUMBER = C.CUST_NUMBER
AND C.CUST_NUMBER = CA.CUST_NUMBER

Any pointers are appreciated.

Does anyone has any ideas?
My biggest problem here is I needed to perform LEFT OUTER JOINS across unrealted domain objects? If it's inner join I can get away with "=" in WHERE clause?

you have to map a native SQL query for that.
session.createSQLQuery(...)

Hi Emmanuel,
That is what we have been trying...
But one issue. The database doesn't return the field names as per the aliases (to the columns specified). The return field names are converted to UPPER CASE. We'll have to create a bean with property names in UPPER CASE to skirt the issue.

Also Hibernate 3.2.0 does not support
sess.createSQLQuery("SQL WITH COLUMN ALIASES GOES HERE").getReturnAliases

Thanks.

http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#propertyresults might help