Alexander Walfridsson Alexander Walfridsson - 6 months ago 10
MySQL Question

Selecting table attributes not part of relation table

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.

edit:

Student.
columns:
Student id varchar primary key
name and surname varchar

Course.
columns:
course id int auto-increment
course name

StudentCourse
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';


This obviously doesn't work. I have also tried comparing the relation table with the student table to see if the students id does not exist in StudentCourse table.

If I select * from StudentCourse I get all students that have a course.
ex:
courseid: 1 studentid: 199502159292

Now I want to select all the students from Students table that do not have a course

ex:
studentid: 199909091414 name: carl surname:peterson

The student that have a course should not be visible.

Edu Edu
Answer

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.