HQL to get elements that possess all items in a set

Question

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;
}

Answer 1

select m from Member m left join m.awards as a where a.name in ("Trophy","Ribbon") group by m having count(a)=2