I'm sure there has to be an easy way to do this, but, I'm just not seeing it right now.

I'm trying to do something like the following, but, it's not working.

SELECT c FROM mypackage.Course c WHERE c.sections.term.startTime > '2004-12-01'

The way my model is setup, a course has many sections, and a section has a single term which has a Date property named startTime.

I want to do a query that returns all courses that have a section that is in a term that has a start time greater than a given date.

The problem is that I don't know what the syntax is for dealing with "sections" which is a collection. I tried doing an INNER JOIN which worked. However, that's not really what I'm looking for as it gives me duplicate course objects.

I also looked at using ELEMENTS(), but, in that case I would need to do something like ELEMENTS(c.sections).term.startTime which doesn't work.

Can anyone help?

It looks like I can do what I want using something like the following using the DISTINCT keyword.

SELECT DISTINCT c FROM Course c INNER JOIN c.sections s WHERE s.term.startTime > '2004-12-01'

I was assuming that HQL had something like the following which you can do in EJBQL. i.e. an IN()

SELECT OBJECT(c) FROM Course c, IN(c.sections) s WHERE s.term.startTime > '2004-12-01'