Register / Login
this forum made possible by our volunteer staff, including ...
Roel De Nijs
Stephan van Hulst
Object Relational Mapping
EJB and other Java EE Technologies
Please help with HQL
posted 3 months 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)
Don't get me started about those stupid
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