Hi,

I did some searching on the forum, but couldn't find an answer to my problem.

I have the following situation:

Table: Course Cols: CourseId, Name

Table Lesson (most of the time multiple per course) Cols: LessonId, CourseId, Begin (DateTime), End (DateTime)

Now I want to retrieve all courses that are currently running.

Therefore I need a query which does something like this:

Get all Courses of which the first Lesson.Begin <= DateTime.Now AND the last Lesson.End >= DateTime.Now.

Is there a way to perform this in 1 query?

Thanks, Gab

Hi Gabriel,

This pseudo-sql should work:

SELECT * 
FORM Course C
WHERE 
   CourseId IN
   ( SELECT CourseId 
      FROM Lesson L
      WHERE 
         L.CourseId = C.CourseId
         AND Begin <= someParamNow
         AND End >= someParamNow
    )

Here you should use FieldCompareSetPredicate

HTH.