CODEWITHSUNDEEP
sqlhibernatehql
JayPea
JayPea

Reputation: 9691

HQL: Is it possible to perform an INNER JOIN on a subquery?

Diagram

The diagram above is a simplified version of the database structure that I use to log item locations through time. I wrote the following SQL query which returns the current item inventory of each location:

select * 
from ItemLocationLog l
inner join 
(select g.idItemLocationLog, max(g.dateTime) as latest
from ItemLocationLog g
group by g.idItem)
as i 
on l.idItem = i.idItem and l.dateTime = i.latest

The problem I'm having is that I want to convert that to HQL, but I haven't found the syntax to perform an INNER JOIN on a subquery, and it seems like this is not supported. Is there a way to convert the above to HQL (or a Criteria) or will I have to use a standard SQL query in this case? Thanks.

Upvotes: 19

Views: 28650

Answers (1)

Arkaine55
Arkaine55

Reputation: 548

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries

Note that HQL subqueries can occur only in the select or where clauses.

You can rewrite the query so that the subquery is part of the where clause instead. Referencing the l.idItem in the subquery

Upvotes: 16

Related Questions