HQL Equivalent of SQL Contains

I'm trying to write an HQL query to select objects which contain an object in a child collection.

Example:

Contest Object
ContestID
ContestName
RequiredCountries -> one to many collection of Country objects

Country Object
CountryCode
CountryName

The sql equivalent of what i want:

SELECT * FROM CONTEST C
WHERE C.CONTESTID IN(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON
CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA')

OR

SELECT * FROM CONTEST C
WHERE EXISTS(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA' AND CC.CONTESTID=C.CONTESTID)

I have this hql, which works, but seems like not a good solution-

from Contest C
where (from Country where CountryCode = :CountryCode) = some elements(C.RequiredCountries)

I also consider joining with Country, but since I don't have an object class to represent the relationship, I wasn't sure how to join in HQL.

Anyone have any ideas or suggestions? This should be easy.

From stackoverflow

• try this:

  from Contest Ct, Country Cr
  where Cr.CountryCode = :CountryCode 
      and Cr.Country in elements(Ct.RequiredCountries)
  

  Related article

  Brian : Exactly what i needed, creates an IN statement in SQL