EJBQL IN or not IN?
I'm trying to develop an EJBQL query, and I can't seem to express what I want - there may be a way, please let me know what you think.
The schema: I have a table called 'Vouchers', and a table called 'Courses'. There is a many-to-many relationship between the two, represented by a join table - 'VouchersCourses'. But some vouchers always relate to all courses; these vouchers have field 'courseCoverage' set to 3, and are not represented in the join table.
The finder: I want to find all the vouchers for a given courseID. The result should include all the vouchers that specifically reference the course, plus all the vouchers that are valid for all courses.
I tried the following:
* @ejb.finder
* view-type ="local"
* signature="Collection findByCourse(java.lang.String crsId)"
*query="SELECT DISTINCT OBJECT(v) FROM VoucherSchema v, IN (v.courses) AS c WHERE v.courseCoverage = 3 OR c.id = ?2"
But the resulting Collection ignores all those with courseCoverage = 3. I guess this is because these vouchers are not IN v.courses!
How do I retrieve all the relevant vouchers in v.courses, PLUS all those that are not in v.courses, but have courseCoverage = 3?
Thank you for your help.
Bruno.
