I have employees and skills:

EMPLOYEE (employee_id number, name varchar2(50))
SKILL (skill_id number, name varchar2(50))

.hbm mapping defines <set name="skills"> for the Employee class using many-to-many association table:

EMPLOYEE_SKILL (employee_id number, skill_id number)

The question: is there a _database independent_ way of creating HQL query "find all employees who has ALL the skills 1, 2, 3, 4" where 1,2,3 and 4 are skill_ids? (of course, number of IDs in the query varies)

Currently I use:

select emp.id from eg.Employee as emp
where emp.id in (
select emp2.id from eg.Employee as emp2
join emp2.skills as skill
where skill.id in (:idList)
group by emp2.id
having count(skill.id) = :listSize)

But this will not work on MySQL for example (because of nested query). Also this approach joins all three tables which is not absolutely necessary (because SKILL table is not needed actually).

Is there a better way to work with collections?

What on earth is the subquery for???

Subquery finds IDs of the matching employees.
"Outer" query loads these employees.

It seems I can not do it without subquery because if I simply use

select emp2 ...
...
group by emp2
...
having count ...

Hibernate creates query with GROUP BY EMPLOYEE_ID and Oracle refuses to SELECT any column except for EMPLOYEE_ID because of "not a group by expression". I do not remember exactly the error text but the idea is understandable - only columns from GROUP BY expression may be selected.