I want to check if any promoter in a collection of promoters is contained by another collection of promoters

this is the query but the DB i need to use doesnt support sub-selects Mysql 4.0, how can I achieve this result without needing sub-selects?

from Promotion p where :promoters in elements(p.promoters)


Promotion.promoters is a many-to-many collection

If your association is bidirectionnal you can check the size of promoter.promotions.

I think this is not your case so maybe you can count the number of promotion that holds your promoter :

select count(*) from Promotion promotion join promotion.promoters promoter where promoter=?

I never tried this query so I don't know if it works.

i've figured out how to get this to work and am now trying to make a Criteria query work instead of straight up HQL

this is the query so far:

select distinct p from Promotion p join p.promoters as promoter where promoter in (:promoters)

i've had no luck with the Criteria querys though

Maybe something like

session.createCriteria(Promotion.class)
.createAlias("promoters", "promoter")
.add(Expression.in("promoter", ...)

Distinct seems to be impossible with the criteria api (http://forum.hibernate.org/viewtopic.ph ... a+distinct) but maybe you can achieve it at java level with a set