So I am looking for a way to select the attributes of a table that are not part of a relation table.
I have 3 tables, Course, Student, and the relation table StudentCourse.
StudentCourse has foreign keys courseid and personid that are primary keys from the other tables.
I'd like to know if I can via sqlquery select the students that do not belong to a course.
Student id varchar primary key
name and surname varchar
course id int auto-increment
courseid int primary key foreign key
studentid varchar primary key foreign key
This is not correct code but it should help you understand the parts of the tables.
I have tried:
SELECT Student.studentid FROM Student INNER JOIN StudentCourse ON Student.studentid = StudentCourse.studentid WHERE StudentCourse.studentid ='null';
To select all the students enrolled in a course, you can do this:
SELECT student_id FROM StudentCourse WHERE course_id=*<value>*
I opted for the simplest approach, that is selecting by ID. If you need to select by name, or other Course column, you should use:
SELECT sc.student_id FROM StudentCourse AS sc INNER JOIN Course AS c ON sc.course_id=c.course_id WHERE c.course_name=*<value>*
Now, the complete query:
SELECT * FROM Student AS s WHERE s.student_id NOT IN ( SELECT student_id FROM StudentCourse WHERE course_id=*<value>* )
This will select all students in the Students table that are not enrolled in the course.