Re: [eclipselink-users] JPA Filter for SQL NOT IN with subquery

[Tom Ware <tom.ware@xxxxxxxxxx>]

The problem is with "s.projekte.id".  You cannot use dot notation to navigate a 
xToMany relationship.  You will need to just join.

Try something like this:

SELECT p
from Projekt p
WHERE p.id NOT IN
(SELECT
p.id FROM Szenario s join s.projekt p

-Tom

gkubesch wrote:
> Hi there!
>
> I have a problem to build the following sql query (on Oracle) with the
> standard JPA 2.0 filters.
>
> SELECT *
>   FROM projekt p
>  WHERE p.id NOT IN (SELECT projekte_id
>                     FROM szenario_projekt)
>
> I need to get all projekts which are not referenced by any szenario. 
>
> There is a many-to-many relation between projekt and szenario, so the
> projekt_id must not be found in the table szenario_projekt.
>
> JAVA Classes: [Projekt]*-*[Szenario]
> Oracle Tables :[Projekt]-*[Szenario_Projekt]*-[Szenario]
>
>
>
> Mapping in Projekt.xml
> <many-to-many name="szenarien"
> target-entity="de.znt.nsi.biz.arbeitsplanung.Szenario"
> mapped-by="projekte"/>
>
>
> Mapping in Szenario.xml
> <many-to-many name="projekte"
> target-entity="de.znt.nsi.biz.projekte.Projekt" />
>
>
> I tried to use the IN-Expression as follows
> Query q = em.createQuery(" SELECT p from Projekt p WHERE p.id NOT IN (SELECT
> s.projekte.id FROM Szenario s )",Projekt.class);
>
> which produced the following error
>
> Exception Description: Error compiling the query [ SELECT p from Projekt p
> WHERE p.id NOT IN (SELECT s.projekte.id FROM Szenario s )], line 1, column
> 53: invalid navigation expression [s.projekte.id], cannot navigate
> collection valued association field [projekte].
>
> Is there any possibility to create a not in SQL Query using JPA 2.0 for
> many-to-many relationships, or is there a special way to do this using
> Eclipselink?
>
> I am thankfull for any help,
>
> George