I have been trying to solve the following problem using Hibernate 2.1.7

First here is the data structure.

I have the following tables:
- Student table that has 3 fields: id, name and age
- Course table that has 3 fields: id, name, level
- StudentCourse that has 2 fields: studentId, courseId

Below is an example of what the database may contain
- Student id: 0
- name : John
- age : 22
- courses :
- MATHEMATICS level 3
- MATHEMATICS level 4
- BIOLOGY level 2
- ENGLISH level 1
- HISTORY level 3
-
- Student id: 1
- name : Joe
- age : 18
- courses :
- BIOLOGY level 1
- MATHEMATICS level 3
- MATHEMATICS level 4
- HISTORY level 2
- HISTORY level 3
- ENGLISH level 2
- ENGLISH level 3
-
etc...

So the class Student and Course are defined as followed:

public class Student {

private Integer id;
private String name;
private int age;
private Set courses;
...
}

public class Course {

private Integer id;
private String type;
private int level;
private Set students;
...
}
And the mapping is :

<hibernate-mapping>

<class name="com.mytest.Student" table="student">

<id name="id" type="java.lang.Integer" column="id">
<generator class="identity" />
</id>

<property name="name" type="java.lang.String" column="name" length="50" not-null="true"/>

<property name="age" type="int" column="age" not-null="true"/>

<set name="courses" table="studentCourse" cascade="all">
<key>
<column name="studentId" not-null="true"/>
</key>
<many-to-many class="com.mytest.Course">
<column name="courseId" not-null="true"/>
</many-to-many>
</set>

</class>

</hibernate-mapping>

<hibernate-mapping>

<class name="com.mytest.Course" table="course">

<id name="id" type="java.lang.Integer" column="id">
<generator class="identity" />
</id>

<property name="name" type="java.lang.String" column="name" length="50" not-null="true"/>

<property name="level" type="int" column="level" not-null="true"/>

<set name="students" table="studentCourse" cascade="all">
<key>
<column name="courseId" not-null="true"/>
</key>
<many-to-many class="com.mytest.Student">
<column name="studentId" not-null="true"/>
</many-to-many>
</set>

</class>

</hibernate-mapping>

Now here is the 2 problems I try to solve:
- I want to get a repartition per groups where a group is defined by the students going to a given list of courses
For example, I would like to get data so that I can display the following:
number of students | BIOLOGY level | MATHEMATICS level
2 | 1 | 1
3 | 1 | 2
3 | 1 | 3
1 | 1 | 4
2 | 2 | 1
2 | 2 | 2
1 | 2 | 3
etc...
- I also want the list of the students going to a given group (ie the list of 3 students going to BIOLOGY 1 and MATHEMATICS 3)

I tried get the list of such students using HQL and filters but I didn't succeed yet (I didn't succeed to do it in an
"efficient" way).
I don't want to do any processing myself (or not too much). I would like to find a way to get that list directly out of
the database using hibernate.

I keep working on this but if any of you has an idea how to do this elegantly and efficiently, I'll be happy to hear you.

Thanks.

Richard