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

Question

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.

Answer 1

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