What are the best approach to retrieve data from multiple tables with join conditions. (If you want do not want to use HSQL much).

Is only HSQL is the only option to get data from multiple tables when you have join like
Example Select empno, ename, dept from emp, dept where emp.empno = dept.empno

Can we give multiple tables with CreateCriteria ?

Please let me know your suggestions. Right now we are not using Hibernate 3 yet.

Describe the realations in your hbm.xml file and navigate them in Java. That's what a ORM tools are made for.

Only if you have many objects on the n side of a relation you have to load them with restricting HQL or Criteria API in order not to hit any performance/memory problems. But having the relations described in your hbm.xml file still helps a lot here.

HTH
Ernst

If you need to get data from multiple tables :- > Is HSQL is only option or can we use any other approach?

Ok,
I got my answer.

I can use createCriteria to retriev data from mulitple tables.