I have a query that I can't figure out how to write. I'm using 1.0.4 and PersistenceBroker. Here's my situation:
Project has a Collection of Activities; this is a non-decomposed m:n via ACTIVITY_PROJECT. Activity has attribute 'conclusionDate'. I want to find all Projects for which every Activity has a non-null conclusionDate. In SQL, here's the query I'm aiming for: SELECT DISTINCT A0.PROJECT_ID, count(A2.ACTIVITY_ID) FROM PROJECT A0 INNER JOIN ACTIVITY_PROJECT A1 ON A0.PROJECT_ID=A1.PROJECT_ID INNER JOIN ACTIVITY A2 ON A1.ACTIVITY_ID=A2.ACTIVITY_ID WHERE NOT EXISTS (SELECT B0.ACTIVITY_ID FROM ACTIVITY B0, ACTIVITY_PROJECT B1 WHERE B0.ACTIVITY_ID=B1.ACTIVITY_ID AND B1.PROJECT_ID=A1.PROJECT_ID AND B0.CONCLUSION_DATE IS NULL); Here's pseudocode for what I'm trying to do: subCrit = conclusionDate is not null // This is the problematic line - how to join to the parent query? subCrit.addEqualToField("activityId", Criteria.PARENT_QUERY_PREFIX + "activityId") ReportQueryByCriteria subQuery = QueryFactory.newReportQuery(Activity.class, subCrit); subQuery.setAttributes({ "activityId" }); crit = new Criteria(); crit.addNotExists(subQuery); What I can't figure out is how to get the join right in the subquery. Any suggestions? thanks, -steve Steve Clark ECOS Development Group [EMAIL PROTECTED] (970)226-9291 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]