openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daryl Stultz <da...@6degrees.com>
Subject Re: Left join with multiple criteria
Date Fri, 11 Sep 2009 18:31:56 GMT
On Fri, Sep 11, 2009 at 2:15 PM, Wes Wannemacher <wesw@wantii.com> wrote:

> One thing that helped me (I'm not exactly sure if it applies here
> because I can't tell if this is meant to be a one-to-one or
> one-to-many type relations between your entities)


b to c is one to many.


> is that I had to
> quit thinking of my data in terms of tables (sets).


Yeah, I did a lot of brain rewiring at first, here comes some more.


> In your case, do
> you really need to retrieve all three fields? Or, can you rework your
> exercise to think of it more like this -
>
> SELECT a FROM A a WHERE a.b.c.id = ?1
>
> b is not terribly important, I just needed it to show the multiple join
criteria. What I need is all A's that meet some criteria, plus all C's for
each A also meeting some criteria. So I need the A's even if there are no
C's.

Let me put it another way. Let's say A to C is one to many. (A.cs is a
collection of C.)

select a, c
from A as a
left join a.cs as c
where a.p1 = 2 and c.p1 = 3
The above won't return A's that have no C's (or no C's with p1 = 3). I need
all A's with p1 = 2 regardless of the existence of related C's with p1 = 3.

-- 
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