Consider the classic Student -< Enrolment >- Course M-M relationship.

Im using <idbag> to hide the underlying associative table, so Student has a List of Courses.

Say i have a list of courses and i want to find all the students taking any of those courses.

Easy:

But, what if i want to find the students who are taking all the papers?

I've been able to handle the case where my list is a know length in a rather ugly manner.

select distinct s.name from Students as s join s.courses as c1 join s.courses as c2 where c1 = :cid1 and c2=:cid2

q.setParameter("cid1", l.get(0));
q.setParameter("cid2", l.get(1));

Does anyone know how i can cope with this an a general, any number of courses manner?

I'm running MySQL 4.0 with InnoDB, in case it's relevant to the possible solutions (availablity of subselects etc).

TIA
dan.[/code]

You can use a count() in the select clause.

Cheers, that's starting to make sense.

So i would get a result for each student who is taking any paper, then iterate through them and get the ones where count() = l.size().

Can i use it in the where clause too, so the query only gives me the results i want?