HQL : inner join

Question

I want to do an inner join in my hql query but i've got this error :

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

SELECT new myDto(
                col1, col2
            FROM table1,table2
            INNER JOIN (
                SELECT foo, bar, ...  max(nb) as numberBar
                FROM table1
                GROUP BY foo, bar ...
             )  myAlias

             ON table1.foo = myAlias.foo
             AND table1.bar = myAlias.bar
             ...

The query works in sqldeveloper

score 0 · Answer 1 · answered Feb 13 '17 at 14:22

Just checking the query you have posted, I think you have to close the parenthesis of new myDto after col2, because in that query you have posted is not closed:

SELECT new myDto(
                col1, col2)
            FROM table1,table2
            INNER JOIN (
                SELECT foo, bar, ...  max(nb) as numberBar
                FROM table1
                GROUP BY foo, bar ...
             )  myAlias

             ON table1.foo = myAlias.foo
             AND table1.bar = myAlias.bar
             ...

Actually, HQL seems to not support `inner join` with `select` :/ . I'll post my workaround. thanks you — ramsey_lewis, Feb 13 '17 at 14:34

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

Hibernate doesn't seem to support inner join with followed by select Inner join with select on HQL I manage to make it work :

 SELECT new myDto(
  //usual colums selection here
  from table1, table2 
 where ...
 AND ....

// solution  here without inner join
AND (  table1.foo, table1.bar, nb)
IN (select foo, bar, max(nb) as number
    from table1     
    group by foo, bar)

edited May 23 '17 at 11:53

Community

1
1

answered Feb 13 '17 at 14:42

ramsey_lewis

558
8
25

HQL : inner join

2 Answers2