Switch a column when joining two SQL tables

Question

The scenario is if I have a student table and they have 4 classes and they only know the room number. There are also two tables, one with the teacher's name and room number, the other with the teacher's name and the subjects they teach. Now the students want to know who is their math teacher, they just want the student name and the math teacher name.

This is a fictional scenario for a voucher project I'm working on. I've gotten it to work in many situations, but it's very slow. The case condition for creating the new column doesn't slow down anything and I left join the tables using the same case condition since we don't know which column to relate the student table to the teacher table. The case condition in the left join seems to be causing the trouble, is there any other method I can use to get the same result without the delay?

Answer 1

I think the reason you're having so many problems is that your architecture isn't very good. Specifically the schema of the students table where there is a column for each course/room number.

I first used a subquery to correct this problem, for example:

SELECT `Student Name`, 1 as Period, `1st Room#` as Room FROM students
UNION ALL
SELECT `Student Name`, 2 as Period, `2nd Room#` as Room FROM students
UNION ALL
SELECT `Student Name`, 3 as Period, `3rd Room#` as Room FROM students
UNION ALL
SELECT `Student Name`, 4 as Period, `4th Room#` as Room FROM students

"Unpivoting" like this will give you a nice clean Student Name | Period | Room schema that will make solving this problem much easier.

SELECT `Student Name`, Subject.`Teacher Name`
FROM
    (
      SELECT `Student Name`, 1 as Period, `1st Room#` as Room FROM students
      UNION ALL
      SELECT `Student Name`, 2 as Period, `2nd Room#` as Room FROM students
      UNION ALL
      SELECT `Student Name`, 3 as Period, `3rd Room#` as Room FROM students
      UNION ALL
      SELECT `Student Name`, 4 as Period, `4th Room#` as Room FROM students
    ) students
    INNER JOIN teacherRoom
       ON students.Room = teacherRoom.`Room Number`
    INNER JOIN teacherSubject
       ON teacherRoom.`Teacher Name` = teacherSubject.Subject
WHERE teacherSubject.Subject = 'Math';