0

I have query in jpa like this(didnt work):

entityManager.createQuery("select new pl.proama.wd.model.PolicyDTO2(pol.policyID) from Policy pol LEFT JOIN pol.claim c  WHERE c.claimID=:id").setParameter("id", "20700003915").getResultList();

and database table like this:

Policy table -->(Policy_id(pk), other colums)

Claim table --> (Claim_id(pk),Policy_id,other colums)

One policy can join to many claim

Normal plsql query(work):

Select pol.POLICY_ID 
from Policy pol LEFT JOIN CLAIM c 
on(c.POLICY_ID=pol.POLICY_ID) 
WHERE c.CLAIM_ID='20700003915';

I think I make mistake in class Policy where I join Claim class.

@OneToOne
@JoinColumn(name = "POLICY_ID")
private Claim claim;

What I'm doing wrong?

hheroinMe
  • 23
  • 1
  • 2
  • 5

1 Answers1

1

Move c.CLAIM_ID condition from WHERE clause to ON clause to get true LEFT JOIN behavior. (With c condition in the WHERE clause, it executes as a regular inner join.)

Select pol.POLICY_ID 
from Policy pol LEFT JOIN CLAIM c 
on (c.POLICY_ID=pol.POLICY_ID
    AND c.CLAIM_ID='20700003915');
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • My plsql query work I have problem with jpql query. – hheroinMe Sep 11 '15 at 08:30
  • Do you want LEFT JOIN or INNER JOIN? Either adjust to INNER JOIN, or move the c.CLAIM_ID='20700003915' condition! – jarlh Sep 11 '15 at 08:49
  • It works in SQL but won't work using JPQL. Adding `AND c.CLAIM_ID='20700003915'` condition in **ON** condition list is still not possible. – Mr ASquare May 31 '17 at 10:05
  • @MrASquare - I'm trying to do something similar, adding an AND condition in ON. I don't get an error instead i get what appears to be the query ignoring the AND. Is that what you saw? If so, how did you recommend resolution? – Sal Velazquez Jul 28 '17 at 17:36
  • @SalVelazquez, start your own question instead of as a comment here, and you'll get much better attention! – jarlh Aug 03 '17 at 08:12