Join table and find there min/max(Collection)

Question

I have a structure:

public class ChosenCourseDate {

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name="chosencoursedate_id")
    List<ChosenCourseDatePeriod> chosenCourseDatePeriod;
}

public class ChosenCourseDatePeriod {

    Date startDateTime;
    Date endDateTime;

    @ManyToOne
    @JoinColumn(name="chosencoursedate_id")
    ChosenCourseDate chosenCourseDate;
}

I want to add a new condition into an existed select statement to find objects by earliest startDateTime in ChosenCourseDatePeriod.

1)Select statement:

select registration from CourseRegistration registration
left join registration.chosenCourseDate as chosencoursedate
left join chosencoursedate.chosenCourseDatePeriod as period
where registration.candidate =:candidate
and registration.course =:course
and min(period.startDateTime) >=:now;

Result:

Invalid use of group function

2)Select statement:

select registration, min(period.startDateTime) as earliestDate from CourseRegistration registration
left join registration.chosenCourseDate as chosencoursedate
left join chosencoursedate.chosenCourseDatePeriod as period
where registration.candidate =:candidate
and registration.course =:course
and earliestDate >=:now;

Result:

Unknown column 'earliestDate' in 'where clause', cause: null

3) I decided to use 2 SELECT statements, it helped.

select registration from CourseRegistration registration
    left join registration.chosenCourseDate as chosencoursedate
    where registration.candidate =:candidate
    and registration.course =:course
    and (SELECT min(period.startDateTime) FROM ChosenCourseDate inChosenCourseDate join inChosenCourseDate.chosenCourseDatePeriod period where inChosenCourseDate = chosenCourseDate) >=:now;

What's wrong with my first and second solutions? Does it possible to do only one SELECT ?

score 1 · Accepted Answer · answered Sep 14 '17 at 11:46

You can't use aggregation functions on the WHERE clause, or without a GROUP BY clause. If you want to check against the maximum date, add a GROUP BY clause and use the HAVING for conditions on aggregation functions :

SELECT registration
FROM CourseRegistration registration
LEFT JOIN registration.chosenCourseDate chosencoursedate
LEFT JOIN chosencoursedate.chosenCourseDatePeriod period
WHERE registration.candidate =:candidate
  AND registration.course =:course
GROUP BY registration
HAVING min(period.startDateTime) >=:now;

Your second query is invalid too, it should only work for old versions of MySQL , since you're lacking a GROUP BY clause. The other reason is - you can't use a derived column (column made by you) in the same layer it is being created, so the solution is to wrap it with another select:

SELECT * FROM ( Your query... ) // It is not available inside the query
WHERE earliestDate>=:now; // It is now available

Join table and find there min/max(Collection)

1 Answers1