Join In HSQL

[Kalpesh Rawal]

Hi I would like to join 2 tables using HSQL,but there is no relationship betyween 2 tables.I mean there is no foriegn key relationship between 2 tables?How can i do this?

[saranga rao]

hi,

Can You give the table name ... if possible

its like this,

session.createCriteria(A.class)
.createAlias("B","tm")
.add(Expression.gt("tm.number", 2))
.list();

or

session.createCriteria(A.class)
.createCriteria("B","tm")
.add(Expression.gt("tm.number", 2))
.list();

You can also write same as in SQL queries using createQuery() method

[Kalpesh Rawal]

i have 2 tables ivtxn & atlog.
create table ivtxn(id varchar2(10),idate date);
create table atlog(alogid number(2),atid varchar2(10),atdate date,aturl vachar2(20),atinsinv number(1));

there is no relationship between 2 tables.But you can say like that id and idate of ivtxn is same as atid,atdate of atlog.

My master table is ivtxn.i want all the records of master table and aturl of atlog along with the records for which atisinv=1 and not equal to 1.

[saranga rao]

Hi rawal,

I don't thing this is require an join

here it is simple query...

select ivtxn , atlog.aturl from ivtxn as ivt , atlog as tlog where
tlog.atinsinv like '1' or tlog not like '1'


in Hibernate

it is like this

String sqlquery = "select ivtxn , atlog.aturl from ivtxn as ivt , atlog as tlog where tlog.atinsinv like '1' or tlog not like '1'";


List lst = createSQLQuery(sqlquery+";").addScalar().list();


Regards
saranga