I have following in the hibernate mapping file...

<hibernate-mapping .....>
<class="CustomerImpl" table="cutomer">
<id name="customerNo" not-null="true" unique="true" sql-type="INTEGER">
<column name="c_cno">
</id>
...

<join table="customerExtn">
<key column="cx_cno" unique="true"/>
...
</join>
</class>

<sql-query name="customers.for.address">
<return alias="cust" class="CustomerImpl" />
<![CDATA[
select {cust.*}
from customer cust
where cust.c_cno in (select ca.ca_cno
from custAddresses ca
where lower(ca.ca_address) like lower(:address))
]]>
</sql-query>
</hibernate-mapping>

Now, The problem is my CustomerImpl class maps to 2 tables 'customer' and 'customerExtn'.

When the specified <sql-query> is formed on the run time it looks something like...

select cust.c_cno, cust.c_cname, ..., cust_1_.deptNo
from customer cust
where cust.c_cno in (select ca.ca_cno
from custAddresses ca
where lower(ca.ca_address) like lower(?))

It throws an error saying 'cust_1_' can not be referenced. It is trying refer to the 'customerExtn' table but customerExtn table is not referenced in the 'From clause' inspite of specifying the <join> in the 'CustomerImpl' class.

I have done a nasty hack to make it work. Looks like...

From customer cust join customerExtn cust_1_ on cust.c_cno=cust_1_.ca_cno

But, this is not an ideal solution as 'cust_1_' is generated by hibernate at the run time.

Has anyone come across something of this sort before?
Please do let me know if there is a way around it.
Many Thanks,
Jeevan.