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

[gkubesch <g.kubesch@xxxxxx>]

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.