CODEWITHSUNDEEP
mysqlsqlhibernatehql
leadingSkill
leadingSkill

Reputation: 377

Perform Inner Join on a subquery that has more than one column, in HQL?

I'm trying to join a table with the result of a sub-query (Sub-query returns the result with 3 columns) in HQL but I'm getting a syntax error org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token.

It looks like INNER JOIN doesn't work in HQL like it does in SQL, so I looked at https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries but it didn't help.

HQL Query

SELECT R
FROM Table R
INNER JOIN (
   SELECT T.id.col1, T.id.col2, MAX(T.col3) max_num
   FROM Table T
   GROUP BY T.id.col1
) b ON R.id.col1 = b.id.col1 AND R.col3 = b.max_num
WHERE R.id.col3 = :param
GROUP BY R.id.col1
ORDER BY R.col3 DESC

Actual Result

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 81

Expected Result

Unique Rows that contain the max value of T.col3.

Note: SQL version of the above query works fine.

Upvotes: 0

Views: 548

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

I would write your HQL query as this:

from R as r
where r.id.col3 = :param and r.col3 = (select max(t.col3) from Table t where t.r = r)

This assumes that the T entity has a reference to the R entity called r. If not, then change the above code accordingly.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you have not used any aggregate function so no need group by

SELECT R.*
    FROM Table R
    INNER JOIN (
       SELECT T.id.col1, T.id.col2, MAX(T.col3) max_num
       FROM Table T
       GROUP BY T.id.col1,T.id.col2
    ) b ON R.id.col1 = b.id.col1 AND R.col3 = b.max_num
    WHERE R.id.col3 = :param

    ORDER BY R.col3 DESC

Upvotes: 1

Related Questions