Search code examples
mysqlsqldatabasemysql-workbenchouter-join

Database Query Help needed

So this is the DB that I have: https://i.sstatic.net/qvSOj.jpg

It has been a while since the last time I worked with a MySQL DB, so I had a few issues that I need to solve.

First of all, I need to write a query that can show me both the name of the student and the class where they are registered at. I was trying some things with WHERE, but I was getting lost a lot.

SOLUTION: Using JOIN is the way to go since you need to connect 3 tables.

Solution

  • Start with the first question. We have a many to many relationship where student_class links each student_id with class_id, but the names are stored in the linked student table and class table. So we need to join all three tables.

    SELECT student.name, class.name
  FROM student
  JOIN student_class
    ON student.id = student_class.student_id
  JOIN class
    ON class.id = student_class.class_id
;