EJB-QL query needed

[sbrbot]

I have two CMP entity beans AppUser representing user of application and AppRole representing security roles of application. There is bidirectional many-to-many relationship between these two beans. So each user can be assigned to many roles (method appUser.appRoles() returns collection containig user's roles) and each role can be assigned to many users (method appRole.appUsers() returns collection containing role's users).
The question is how to create EJB-QL which will return a collection of roles not assigned to given user (user passed as an argument to query)? Or vice versa; how to create EJB-QL which will return a collection of users not assigned to given role (role passed as an argument to query)?
I've been trying for few days with many combination (NOT IN, NOT MEMBER OF etc.) but without any success!

[kalyan120]

One way I can think of is (though not exactly what you wanted):

1. Get all the Roles of the given user: appUser.appRoles()
2. Get all the Roles defined: appRole.fetchAllRoles()

Subtract 1 from 2, and you will have, all the roles that are not assigned to the given user.

Moreover, you need to have a method like appUser.fetchUnassignedRoles() to do the above.

Thanks,
Kalyan.