How to have multiple conditions in JPQL join

Question

I want to join two tables using JPQL:

SELECT * FROM A LEFT JOIN 
B ON A.ID = B.A_ID AND B.lng = 'en'

The important part is AND B.lng = 'en'

Is this possible in JPQL?

Have you resolved this? I have a similar issue, posted [here][1]. [1]: http://stackoverflow.com/questions/28633921/atypic-jpa-onetoone-relation — Ronye Vernaes, Feb 20 '15 at 17:01
It works in SQL but can't convert it into JPQL. Adding `AND B.lng = 'en'` condition in **ON** condition list is still not possible using JPQL. — Mr ASquare, May 31 '17 at 10:08

score 9 · Accepted Answer · answered Jun 19 '12 at 14:49

9

JPA 2.0 does not support an ON clause, but the JPA 2.1 draft does.

EclipseLink 2.4 supports an ON clause.

answered Jun 19 '12 at 14:49

James

6

HQL supports it using the `with` keyword: `select a from A a left join a.b b with b.language = 'en'` – JB Nizet Jun 19 '12 at 17:10

score 1 · Answer 2 · edited May 23 '17 at 10:29

1

Yes it is possible, there is a similar question here. However, if your entities are mapped, you should be able to access them in a query.

edited May 23 '17 at 10:29

Community

answered Jun 19 '12 at 12:20

John Kane

hi john thanks for the feedback, but where is the example where the `AND` part is in the left join ... NOT in the where – Jeremy S. Jun 19 '12 at 12:29
I am not sure what you mean by the and part being in the left join and not in the where? – John Kane Jun 19 '12 at 12:56
Do you mean having two join conditions? – John Kane Jun 19 '12 at 12:59
I did a quick search (assuming that you meant conditional join) http://www.java2s.com/Questions_And_Answers/JPA/Join/condition.htm – John Kane Jun 19 '12 at 13:07
yeah I meant a conditional outer join – Jeremy S. Jun 20 '12 at 11:15

2 Answers2