JPQL left outer join does unnecessary joins

Question

I've got the following JPQL :

SELECT a.b.id, a.b.name, a.c.id,a.c.name 
 left join a.b left join a.c
 group by a.b.id,a.b.name,a.c.id,a.c.name

now b and c are both referencing the same table. the generated SQL is doing the left join I asked, and another join for a.b.name and a.c.name (which is unnecessary because the left join includes the name, and it retrieves more results than expected) how do I make the SQL generated not include the unnecessary join?

1 solution came up is not select the names and retrieve them individually by a different query.. but it's not the most elegant way I suppose..

(btw I tried selecting a.b,a.c and group by a.b,a.c but it throws ORA not a group by expression because the generated sql retrieves all rows but group by is only by ID) and the left join is necessary since I want to allow null values.

Thanks a lot.

JB Nizet JB Nizet · Accepted Answer · 2014-06-18T11:30:12

SELECT a.b.id, a.b.name, a.c.id,a.c.name

The above implicitly creates an inner join between a abd b,a nd another inner join between a and c. The query should be

select b.id, b.name, c.id, c.name
from A a
left join a.b b
left join a.c c

The group by clause doesn't make any sense, since you have no aggregate in your select clause. group by would be useful if you had, for example

select b.id, b.name, c.id, c.name, count(c.foo)
from A a
left join a.b b
left join a.c c
group by b.id, b.name, c.id, c.name

JPQL left outer join does unnecessary joins

1 Answers