Return all courses a student is taking (Many to Many SQL Database example)

Question

I'm fairly new to MySQL, and trying to understand the many-to-many relationship since these examples can popup in interviews

There are 3 tables, and since a Student can have many courses and a Course can have many students, this is a Many-to-Many relationship right?

The tables are Student- has student ID, name, date of birth, and department. Courses- Has ID, Name of course Student_Courses- Has student_id, course_id

How would I display these 2 questions-

1) Given a studentID, return all the names of the courses the student is taking 2) Return the name of students who is taking X amount of courses or more (Ex. 4 or more courses).

Im trying to write queries on these, but I'm stuck...

Toby Inman Toby Inman · Accepted Answer · 2018-04-04T17:18:13

In the case of selecting all of the courses for a given student ID you could try the following, which will return one row for each Course a Student is associated with.

select
  s.name as StudentName,
  c.name as CourseName
from `Student` as s
  inner join `Student_Course` as sc on (sc.student_id = s.ID)
  inner join `Course` as c on (c.ID = sc.course_id)
where
  (s.`ID` = 'given_Student_ID_here')
;

As for selecting a list of the names of Students taking N or more courses, for this you might use an aggregating sub-select as a WHERE clause in which we reference one of the outer tables (i.e. [Student]) so that the result of the aggregation is personalised per Student record:

select
  s.name as StudentName
from `Student` as s
where
  (
    (
      select count(*)
      from `Student_Course` as sc
        inner join `Course` as c on (c.ID = sc.course_id)
      where (sc.student_id = s.ID)
    ) >= 4
  )
;

You might also consider an alternative approach to this second problem by using the GROUP BY and HAVING clauses:

select
  s.name as StudentName
from `Student` as s
  inner join `Student_Course` as sc on (sc.student_id = s.ID)
  inner join `Course` as c on (c.ID = sc.course_id)
group by
  s.name
having
  count(*) >= 4
;

Return all courses a student is taking (Many to Many SQL Database example)

1 Answers