I've read the FAQ at http://www.hibernate.org/118.html that describes how to place a condition on a collection size using MySQL (which does not support subqueries).

My problem is that I have to qualify by two collection sizes.

Briefly, I have a Parent object that has two collections - childset1 and childset2 (both are implemented as separate tables that have a many-to-one back to the parent). I need to select parents in which both collections have a non-zero count.

My original HQL (before I was reminded that MySQL versions < 4.1 do not do subqueries) was:

select from parent where childset1.size > 0 and childset2.size > 0

Not sure how to apply one condition after the other. I thought maybe I could use a collection filter to apply the second condition, but the syntax does not support it. Any ideas on how to do this.

The approach I'm using now is to use the tip from the above FAQ to qualify on the first set:

select parent from parent, child1 in parent.childset1
group by parent having count(child1) >= 1

The iterate through this collection checking the "childset2.size()" function for each item in the collection.

This just seems awfully inefficient.

Thanks for any and all ideas/suggestions.