SQL join two tables but one column switches-CodePudding

The scenario is if I have a table of students, who has 4 classes and they only know the room numbers.There are also two more tables, one is of the teacher name and room numbers, and the other one is teacher name and the subject they're teaching. Now the students want to know who is their math teacher and they only want Student's Name and Math Teacher's Name.

This is a made up scenario for a credential project I'm working on. I've got it to work using a lot of case conditions but it is extremely slow. The case conditions used to make a new column doesn't have any slow down and I use the same case condition to left join the tables since the we don't know which column to relate the student table with the teacher tables. And that case condition in the left join is the one seems to cause the trouble, are there anything else I can use to get the same result without the delay?

CodePudding user response：

I think the reason you are running into so many problems is because your schema is not great. Specifically your schema for the students table with a column for each course/room number.

I would start by correcting this using a subquery like:

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 get you a nice clean Student Name | Period | Room schema that will make solving this 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';