hi rob. your example does not work for me because I have no attribute 'schedule' in table Activity, only an attribute 'activityId' in table schedule as fk. However, your code inspired me so I was able to set it up by myself, although I still use the IN query. thanks.
-----Urspr�ngliche Nachricht----- Von: Coup, Robert Muir [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 1. Februar 2004 23:59 An: OJB Users List Betreff: RE: How to setup this query with OJB-PB Hi, Minor assumption/clarification - you want to find all activities with no schedules (where activities is on the 1 side and schedules are on the N side). I'm struggling with something similar (only its nasty m:n) so you're in luck: crit.addIsNull("schedules.uid"); // pk of schedule newQuery = QueryFactory.newQuery(Activity.class, crit, true); newQuery.setPathOuterJoin("schedules"); // collection name as declared in repository This will effectively do a SELECT activity.* FROM activity LEFT OUTER JOIN schedule ON (activity.id = schedule.activity_id) WHERE (schedule.id IS NULL) Which should return what you want. Also much nicer/faster than an IN query. Good luck, Rob :) > -----Original Message----- > From: Dirk Manske (Service Respond) > [mailto:[EMAIL PROTECTED] > Sent: Monday, 2 February 2004 11:49 a.m. > To: 'OJB Users List' > Subject: How to setup this query with OJB-PB > > > Hi, > > I am using PB API and have two classes Activity and Schedule with 1:N > association. Both are declared in the repository-user.xml with a > collection-descriptor as reference. Now I want to process a query > which results in a collection with all activities where no schedule > has been set. I have read the Query examples carefully but I just > don't get it. Could someone help me with the following sql statement: > > SELECT * FROM Activity As act > > WHERE act.ACTIVITY_ID NOT IN > > (SELECT ACTIVITY_ID FROM SCHEDULE WHERE ACTIVITY_ID NOT NULL); > > > Looks so simple, but I cannot figure it out. How to set up this > statement using the Criteria class? > > Hope someone can help. thanks, > > Dirk > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
