Jakob, If you can't do a subquery, then an outer join seems to be in order, i.e:
SELECT DISTINCT A0.PK,<other attributes here> FROM Table A0, Table A1 WHERE A0.PK = A1.FK(+) and A1.PK is null order by A0.PK Ken -----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 9:49 AM To: OJB Users List Subject: Re: Difficulty checking for NULL / NOT_NULL collections hi ken, afaik the ODMG parser does not use report queries. but i'm looking for a way to make those kind of queries simpler, i'd like to hide the additional report query. if you're an sql crack could you please provide some sql samples for those kind of queries. another thing is the sub select which is not always supported :( jakob ----- Original Message ----- From: "Ken Robinson" <[EMAIL PROTECTED]> To: "'OJB Users List'" <[EMAIL PROTECTED]> Sent: Friday, December 13, 2002 5:22 PM Subject: RE: Difficulty checking for NULL / NOT_NULL collections > Thanks very much Jakob, that got me the result I was looking for. > Any chance this is doable under OQL? > > -----Original Message----- > From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 13, 2002 4:55 AM > To: OJB Users List > Subject: Re: Difficulty checking for NULL / NOT_NULL collections > > > hi ken, > > have you tried using in with subqueries: > > ReportQueryByCriteria subQuery; > Criteria subCrit = new Criteria(); > Criteria crit = new Criteria(); > subQuery = QueryFactory.newReportQuery(Article.class, subCrit); > subQuery.setColumns(new String[]{"productGroupId"}); > subQuery.setDistinct(true); > crit.addNotIn("groupId", subQuery); > Query q = QueryFactory.newQuery(ProductGroup.class, crit); > results = broker.getCollectionByQuery(q); > > the sample can be found in org.apache.ojb.broker.QueryTest#testSubQuery2() > > i admit doesn't look straight forward but at least it works. if you have a > better/easier solution just let me know. > > hth > jakob > > ----- Original Message ----- > From: "Ken Robinson" <[EMAIL PROTECTED]> > To: "OJB Users List (E-mail)" <[EMAIL PROTECTED]> > Sent: Friday, December 13, 2002 3:00 AM > Subject: Difficulty checking for NULL / NOT_NULL collections > > > > Hi all, > > > > My basic model is as follows: > > > > class A { > > int id; //PK > > ... //a few more attributes > > List b; //collection of class B objects, of which there can be 0:n > > } > > > > I'm having trouble creating the proper criteria to only return the > instances > > of A where > > there are 1 or more instances of B associated (i.e. b.size > 0). The same > > goes > > for the negative form. I've tried OQL, PB with subqueries, and PB with > > exists/not exists > > criteria. About the only way I can get what I want is through QueryBySQL. > > Is there > > something that I'm overlooking here? > > > > Thanks, > > > > Ken Robinson > > > > > > > > -- > > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> > > > > > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> > > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
