CODEWITHSUNDEEP
javamysqlsql
Alexander Walfridsson
Alexander Walfridsson

Reputation: 3

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.

Upvotes: 0

Views: 526

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

The below query will return the Student details, those who are not belong to the Course:

SELECT ST.*
FROM Student ST
LEFT JOIN StudentCourse SC ON SC.StudentId = ST.StudentId 
WHERE SC.StudentId IS NULL

Upvotes: 0

Edu
Edu

Reputation: 2643

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.

Upvotes: 0

Related Questions