Hi. Is there really no way in JPQL to have a left join on a sub-query? It seems that WHERE would always be applied to the product of the join, and never on the joined table. And there doesn't seem to be a way to create a mapped relationship that would be restricted by some sub-query. So the alternative is to have tables for each possible variation, which is not always possible. I found some 2009 threads on this, but they don't have much resolution.
It's generally, IMHO, a quite common case when there are two entities, with one-to-many relationship. If I wanted to find all ALPHA entities that do not have a certain relationship with BETA, in SQL, I would do: select alpha.* from alpha left join (select beta.alpha_id from beta where <constraint on beta>) j on alpha.id = j.alpha_id where <constraing on alpha> Attempting to move the <constraint on beta> outside of the join sub-query will most likely miserably fail, because SQL engines, in general, seem to throw away ALPHA entries that don't match anything in the joined table when WHERE clause has references to BETA (even if you try using BETA.column is null). Thank you, Pawel. P.S. Sorry for off-topic. P.P.S. And what's really the deal with Collection Member Declaration? The Spec basically says that it's "... For example, the [join] query .... can be equivalently expressed as follows, using IN operator: ...". With no more insight, is it just an ability to use an alternative syntax to inner joins?
