HQL: Is it possible to perform an INNER JOIN on a subquery?
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
Hibernate HQL inner join subselect
I think your SQL query can be rewritten to this:
SELECT f.date, f.name, SUM(f.seats)
FROM Foo f
WHERE EXISTS (
SELECT 1
FROM Foo f1
WHERE f.start + f.end = f1.start + f1.end
AND f.date = f1.date
AND f1.date >= (SELECT MAX(earliestDate) FROM Bar)
AND f1.name = :name
)
AND f.date >= (SELECT MAX(earliestDate) FROM Bar)
GROUP BY f.date, f.name
ORDER BY f.date ASC, SUM(f.seats) DESC
This should be simpler to translate to HQL, and it might even be more correct, because your original query seems to create an unwanted cartesian product between the self joined rows.
There's probably an even better way to query this in SQL, without self joining the Foo table, using a single pass through the table. But I'd need to know more about your real world use-case for that, and what RDBMS you're using.
Subselect in LEFT JOIN hql
When you join with that table, you need to use the ON
keyword.
LEFT JOIN (
SELECT d, e, f
FROM table9 as t9
....
) as xx ON xx.prop = t2.prop and xx.prop2 = t4.prop
Also you forgot the =
in the join of the second property.
EDIT:
A subquery must be surrounded by parentheses (often by an SQL
aggregate function call). 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.
From this, you cannot have a sub-query in the from clause.
HQL Subqueries in joins
Unless I'm missing something, your query can be rewritten in SQL as:
SELECT
o.id,
o.amount,
sum(s.quantity*s.price),
sum(s.quantity*i.quantity*i.price)
FROM orders AS o
JOIN ordersections AS s ON s.order_id=o.id
JOIN orderitems AS i ON i.section_id=s.id
GROUP BY o.id, o.amount
In which case it can then be rewritten in HQL as:
SELECT
o.id,
o.amount,
sum(s.quantity*s.price),
sum(s.quantity*i.quantity*i.price)
FROM orders AS o
JOIN o.sections AS s
JOIN s.items AS i
GROUP BY o.id, o.amount
If I am missing something and the above query does not return what you want, you're out of luck with HQL conversion because Subqueries in HQL can only occur in SELECT or WHERE clauses. You can, however, map your query as <sql-query>
- should make no difference at the end.
Related Topics
How to Return a Incremental Group Number Per Group in SQL
Tsql - How to Use Go Inside of a Begin .. End Block
T-SQL Skip Take Stored Procedure
Parallel Unnest() and Sort Order in Postgresql
SQL Server Query Time Out Depending on Where Clause
Export Table Data from One SQL Server to Another
How to Find the Data Directory for a SQL Server Instance
MySQL Select X from a Where Not in ( Select X from B ) - Unexpected Result
Procedurally Transform Subquery into Join
How to Delete from a Table Where Id Is in a List of Ids
SQL Row_Number() Function in Where Clause
Why & When Should I Use Sparse Column? (SQL Server 2008)
Sum of Grouped Count in SQL Query
Rails Find Record with Zero Has_Many Records Associated
Doesn't Linq to SQL Miss the Point? Aren't Orm-Mappers (Subsonic, etc.) Sub-Optimal Solutions