Hello,

I have 3 tables - a student table, a course table and a student_course table.

The table ddl's are as follows:

CREATE TABLE STUDENT
(
studentId VARCHAR2(10) NOT NULL,
studentName VARCHAR2(50) NOT NULL,
PRIMARY KEY(studentId)
);

CREATE TABLE COURSE
(
courseId VARCHAR2(10) NOT NULL,
courseName VARCHAR2(50) NOT NULL,
PRIMARY KEY(courseId)
);

CREATE TABLE STUDENT_COURSE
(
SC_STUDENT_ID VARCHAR2(10) NOT NULL,
SC_COURSE_ID VARCHAR2(10) NOT NULL,
PRIMARY KEY(SC_STUDENT_ID, SC_COURSE_ID));

ALTER TABLE STUDENT_COURSE
ADD CONSTRAINT FK_STUDENTS FOREIGN KEY(SC_STUDENT_ID)
REFERENCES STUDENT (studentId)
ON DELETE CASCADE;

ALTER TABLE STUDENT_COURSE
ADD CONSTRAINT FK_COURSES FOREIGN KEY(SC_COURSE_ID)
REFERENCES COURSE (courseId);

Now, I want to fetch all students who are NOT enrolled in a particular course.

The SQL would be:

Select * from Student s
where not exists (
Select * from Student_course sc
where sc.student_id = s.student_id
and sc.course_id = :course);

In the above SQL, I would pass in the :course parameter.

Is the following HQL an equivalent of the above SQL and if so, is it correct?

from Student s join fetch s.courses courses
where not exists (
from STUDENT_COURSE stdcrse left join stdcrse.course c
where stdcrse.studentid = s.studentid
and c.courseid = :course)


Thank you!

Easy. The best @ManyToMany association is when you don't use @ManyToMany at all.

Just map the StudentCourse table as an entity which has 2 @ManyToOne associations to Course and Student. This way, your HQL query is the same with the SQL one:

Wow! Excellent! Thank you so much. I will try it out.