Register / Login
this forum made possible by our volunteer staff, including ...
Stephan van Hulst
Object Relational Mapping
EJB and other Java EE Technologies
Please help with HQL
posted 1 month ago
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 p.courses courses where not exists ( from STUDENT_COURSE stdcrse left join stdcrse.course c where stdcrse.studentid = s.studentid and c.courseid = :course)
It is sorta covered in the
JavaRanch Style Guide
Boost this thread!
Need help with EJB3.0 Stateless Session Bean code
How to do cross table queries?
Constraint Violation Question
help with PL/SQL
many to many