Inner join with select on HQL

Question

I want to do something like that with HQL:

SELECT *
FROM tableA a
INNER JOIN (select fieldA, sum(fieldB) as sum from tableB) b
ON a.fieldA = b.fieldA and a.fieldC = b.sum;

But this gives an error:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: (...

There is any way to make this using HQL and Hibernate?

Answer 1

try the native SQL solution approach:

need toimport this first:

import org.hibernate.SQLQuery;

then somewhere in your code:

SQLQuery query = session.createSQLQuery(
    "SELECT * FROM tableA a
    INNER JOIN 
    (SELECT fieldA, sum(fieldB) as sum from tableB) b
    ON a.fieldA = b.fieldA and a.fieldC = b.sum"
);

more on this link
and HERE ( Joins in Hibernate Query Language)