HQL: using elements () on objects

Question

Say I have a database with two tables: classes and students. Each table contains metadata on a related topic. For example, classes contain the fields "classid", "name" and "room_number", and students contain the fields "name", "classid", "age" and "height" (for example, a student can only be in one class).

What if I want to write an HQL query to get all the classes that students include with the names "Joe", "Bob" and "Fred"? If students only contained “classid” and “name”, I could write the following in HQL.

from Classes as class where 'Joe' in elements(class.students) and 'Bob' in elements(class.students) and 'Fred' in elements(class.students) 

However, students are an object in this case. Do I need to explicitly make connections?

 from Classes as class join class.students as s1 join class.students as s2 join class.students as s2 where s1.name = 'Joe' s2.name = 'Bob' s3.name = 'Fred' 

I believe that using 'Fred' in elements also does these joins, but it is much more compact to write! Or is there a better way to do this at all?

Answer 1

Use a subselect:

 from Classes c where c.classid in( select s.classid from students s where s.name in('Joe', 'Bob', 'Fred') )