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

["Andrei Ilitchev" <andrei.ilitchev@xxxxxxxxxx>]

Try

SELECT *
FROM projekt p
WHERE NOT EXISTS (SELECT 1
                  FROM szenario_projekt WHERE p.id = projekte_id)

----- Original Message ----- 
From: "gkubesch" <g.kubesch@xxxxxx>
To: <eclipselink-users@xxxxxxxxxxx>
Sent: Tuesday, February 23, 2010 6:46 AM
Subject: [eclipselink-users] JPA Filter for SQL NOT IN with subquery


> 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
>
> --
> View this message in context: 
> http://old.nabble.com/JPA-Filter-for-SQL-NOT-IN-with-subquery-tp27701269p27701269.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users