openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daryl Stultz <da...@6degrees.com>
Subject Left join with multiple criteria
Date Fri, 11 Sep 2009 18:00:17 GMT
Hey, I'm getting crazy on this. I'm trying to convert SQL to JPQL. Suppose I
have this:
select a.p1, b.p1, c.p1
from a
join b on b.aId = a.id
left join c on c.bId = b.id
and c.aId = a.id

maybe it converts to something like this:

select a.p1, b.p1, c.p1
from A as a
join a.b as b
left join b.c

but I'm left without the second join criteria (and I get too much). I
thought this might work:

select a.p1, b.p1, c.p1
from A as a
join a.b as b
left join b.c
where (c.id is null or c.aId = a.id)

but I get too little (no rows with null c)

Is is possible to pull this off in JPQL? (Actually I can't get it to work in
SQL either, I'm using PostgreSQL). This isn't an exact representation of
what I'm trying to do but hopefully you see the issue (where/how to put
additional criteria on left join).

OpenJPA 1.2.1

-- 
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message