Menu
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.
433
A subquery can be used with JOIN operation.
HQL Join : HQL supports inner join, left outer join, right outer join and full join. For example, select e.name, a. city from Employee e INNER JOIN e.
Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. Note that HQL subqueries can occur only in the select or where clauses. Note that subqueries can also utilize row value constructor syntax.
A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery. Joins and subqueries are both used to combine data from different tables into a single result.
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
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
