Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[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.

  FROM projekt p
 WHERE 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"

Mapping in Szenario.xml
<many-to-many name="projekte"
target-entity="" />

I tried to use the IN-Expression as follows
Query q = em.createQuery(" SELECT p from Projekt p WHERE NOT IN (SELECT FROM Szenario s )",Projekt.class);

which produced the following error

Exception Description: Error compiling the query [ SELECT p from Projekt p
WHERE NOT IN (SELECT FROM Szenario s )], line 1, column
53: invalid navigation expression [], 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

I am thankfull for any help,


View this message in context:
Sent from the EclipseLink - Users mailing list archive at

Back to the top