Joining a subquery to parent across non-decomposed m:n

[St...@fws.gov]

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
steve_clark@fws.gov
(970)226-9291


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org