i need a complex inner join like this --> select * from tableA inner join table B on tableA.field1 = tableB.field1 inner join tableC on tableB.field2 = tableC.field2 . how to create the criteria statement for tableC ?

for tableA and tableB, i use sess.createCriteria(TableA.class, tableA).createAlias("tableB","tableB") and working fine


but now, i have statement become complicated, when i have table C
that can onlly join with table B with field call "field2" and my tableA doesn't have any field to join with table C.

Have you tried it with subcriterias instead of aliases ? There you get a new criteria back, on which you should be able to add the join to table C.



This is the NHibernate forum and looking at your sample code I assume, you're are using the java version of hibernate, but the criteria stuff works in the same way, so I hope this helps.

i tried your way , cannot work.

tableC doesnt show up in the sql query in debugging mode


for your info, only in tableB, i have xml that map to tableC . tableA xml doesnt map to table C. is that the cause? there is no field in tableA that able to join with table C. only table b can join with table c

Can you post the mappings ?

in table B (table B and table C, join with field2)
---------------

<many-to-one
name="TableC"
column="field2" insert="false" update="false"
/>



in table A (table A and table B, join with field1)
---------


<many-to-one
name="TableB"
column="field1" insert="false" update="false"
/>

Do you have some expressions on the subcriteria for tableC ? Like



Something like that should create a query over all 3 tables. Can you post your complete criteria ?

crit =sess.createCriteria(tableA.class,"tableA")
.createCriteria("tableB", "tableB")
.createCriteria("tableC","tableC")

.createAlias("tableD","tableD")

.add( Restrictions.like("tableC.field5","ghost"))





--------

this is a complete criteria

in my table A mapping, i map to table B and table D
in my table B mapping, i map to table C


still, on sql debug mode, i dont see tableC show up while, table A, B and D show up.
is it "a must" for table A to map to table C in xml ? i dont have any field in table A that able to map to table C. only table B can map to table C.

It seems strange that .createAlias("tableD","tableD") works at all on the tableC subcriteria, since that does not have a link to tableD. But maybe hibernate walks up the criteria tree.
I'm not sure, what goes wrong ... basically this should work. Try an easier version:



Definitely not, this should work.

sorry.

in my table A xml mapping, i map to both table B and table D. i mentioned that in the last post. u u didnt noticed.


inside tableA xml
-----

<many-to-one
name="tableB"
column="field2" insert="false" update="false"
/>


<many-to-one
name="tableD"
column="field4" insert="false" update="false"
/>

to use

.createCriteria("tableC","tableC")


do i need to create tableC pojo object inside tableA pojo object?

That's not what I'm wondering about.



You're calling createAlias on a new ICriteria instance that was created for tableC. But tableC has no link to tableD or does it ? But maybe hibernate really walks up the criteria tree here.

table C, doesnt have any mapping to any other table.

Have you tried the easier criteria without the tableD link ?

i get this error

could not resolve property: tableC of: com.health.entity.tableA

i already added object tableC inside tableA pojo. but still the same. that error indicate there is no xml mapping in tableA to tableC.