Metehan Aytaç Metehan Aytaç - 8 months ago 62
MySQL Question

about a sql query

I have some table. They have information about students.Like basic student database.
I have course table : course_id , course_name

Student_course : student_num,course_id

student: name,student_num

A student can take more than 1 course.
Student_Course tab:

Student_Num Course

1 2

1 3

2 2

My question is lets we have a course and it's id=2.I want to find the names of students who never take this course.
Is that possible with this tabs?If possible anyone help?

Answer Source

It is possible (and easy). The NOT IN condition is the most likely candidate for a solution. If the student_num in student_course is constrained to not null (as it should be), your query may be as simple as

select student_name
from   student
where  student_id not in ( select student_id
                           from   student_course
                           where  course_id = 2

Now, before you turn in this homework, try to understand how it works. If any part of it is confusing, write back for clarification. For example, think about why this will fail if student_id in student_course may be null. How would you modify this solution to make it work even if student_id may be null in the student_course table? Just turning this in without any thought on your part will not benefit you in any way.