HQL to get elements that possess all items in a set

Posted by Tauren on Stack Overflow See other posts from Stack Overflow or by Tauren
Published on 2010-05-21T02:05:34Z Indexed on 2010/05/21 2:10 UTC
Read the original article Hit count: 531

Filed under:
|
|

Currently, I have an HQL query that returns all Members who possess ANY Award from a set of specified Awards:

from Member m left join m.awards as a where a.name in ("Trophy","Ribbon");

What I now need is HQL that will return all Members who possess ALL Awards specified in the set of Awards.

So, assuming this data:

Joe has Trophy, Medal
Sue has Trophy, Ribbon
Tom has Trophy, Ribbon, Medal

The query above would return Joe, Sue, and Tom because all three possess at least one of Trophy or Ribbon. But I need to return only Sue and Tom, because they are the only ones who possess all of the specified awards (Trophy and Ribbon).

Here's the class structure (simplified):

class Member {
  private String name;
  private Set<Award> awards;
}
class Award {
  private String name;
}

© Stack Overflow or respective owner

Related posts about hibernate

Related posts about hql