With hibernate I cannot use subqueries in from clause. I got an exception telling me that the token "(" is not expected. I read in this forum that hibernate is not implementing subqueries in from clause.
Right, that is not implemented.
So my question ist: what is the alternative?
A join, in your case a selfjoin. :)
I have the following concrete problem:
- I have 1 table with column "from" and "to"
and I want to ask "from A to C" and that the db answers me
"segment A-B, segment B-C"
My SQL is like this:
select s1, s2 from (select * from segment where departure='A') as s1, (select * from segment where destination='C') as s2
In SQL your query using a selfjoin looks like this.
By the way, this is definitely even faster than two subqueries in one select statement.
FROM segment as s1
JOIN segment as s2 ON (s1.destination = s2.departure)
WHERE s1.departure = 'A'
AND s2.destination = 'C'
How can I translate this SQL in HSQL??
I've never tried to use a selfjoin in HQL, but I'm quite sure that this is possible like any other type of join.
Well, if don't get it, you could also use a native SQL query. You could even use your from-clause subqueries within native SQL.
See: Chapter 17. Native SQL
http://www.hibernate.org/hib_docs/v3/re ... /#querysql