CODEWITHSUNDEEP
javahibernateinformixhsqldb
coderslay
coderslay

Reputation: 14370

How to perform outer join in Hsql

I have a query like this

SELECT * from table1 t1,table2 t2, OUTER table3 t3

This works in Informix database but when I use it as Hsql then it is not able to find this "OUTER" keyword only... How to do outer join in Hibernate?

Upvotes: 3

Views: 15349

Answers (3)

fredt
fredt

Reputation: 24352

HSQLDB supports Standard SQL syntax, which is different from your Informix syntax query. Some examples of this syntax:

SELECT * from table1 t1 NATURAL JOIN table2 t2 NATURAL LEFT OUTER JOIN table3 t3

SELECT * from table1 t1 JOIN table2 t2 ON (T1.X = T2.Y) LEFT OUTER JOIN table3 t3 ON (T2.Z = T3.X)

You should adapt your query to the Standard syntax.

The query you reported in comments is translated to Standard SQL this way:

SELECT distinct sd.student_id, ss.sports_id, sf.fee_desc FROM STUDENTDETAILS sd
JOIN STUDENTSPORTS ss ON ss.sports_id = sd.sports_id LEFT OUTER JOIN STUDENTFEES sf 
ON ???

Note you need a condition for the question marks. For example the id in STUDENTSFEES which needs to be equal to the id in STUDENTSSPORTS. sf.some_id = ss.some_id

Upvotes: 5

Brian
Brian

Reputation: 2229

assuming you have your entities and mapping files set up correctly it would be a pretty straightforward "left outer join entity.field as alias"

from the HQL website 

from Cat as cat
inner join cat.mate as mate
left outer join cat.kittens as kitten

HQL website

In your case taking the tables as entities it would be something like from table1 as tab1 inner join tab.table2column as tab2 left outer join tab.table3 column as tab3

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 691735

HQL and SQL are two different languages. SQL uses tables and columns, and HQL uses entities, fields and associations between entities.

Read the Hibernate reference documentation.

Upvotes: 0

Related Questions