Re: [eclipselink-users] Outer joined one-to-many question

[Laird Nelson <ljnelson@xxxxxxxxx>]

On Mon, Aug 19, 2013 at 2:22 PM, Andrei Ilitchev <andrei.ilitchev@xxxxxxxxxx> wrote:

How about:

SELECT p.id
  FROM Parent p

  LEFT OUTER JOIN p.children children
 WHERE
   children IS EMPTY OR
   EXISTS(SELECT child FROM Child child WHERE (child IN (p.children) AND child.x = 'y'))

Thank you.

This seems really complicated (the EXISTS and the subquery).  Could you kindly tell me why the following is not supposed to work:

SELECT p.id

  FROM Parent p

  LEFT OUTER JOIN p.children child

 WHERE p.children IS EMPTY OR child.x = 'y'

...?  Does the query here return true in the non-empty case only when all members of the collection have an x = ''y'?

As a side note, I noticed that none of the examples in the JPA specification actually make use of the outer join they show.  The one that shows up over and over is:

SELECT c FROM Customer c LEFT JOIN c.orders o WHERE c.status = 1 

Note that neither c.orders nor o is referenced.

Thanks again for your time and help,

Best,

Laird

--
http://about.me/lairdnelson