Joining teachers name onto student schedule
On the schedule right now it shows the teacher_id but i'd like to display the teachers name so how would I join that onto Rex's schedule ?
Link to lesson: https://teamtreehouse.com/library/student-schedule
My code at the moment:
SELECT * FROM STUDENTS JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE FIRST_NAME = 'Rex' AND LAST_NAME = 'Rios' ORDER BY PERIOD_ID ASC
Steven Parker203,694 Points
It's a bit tricky because the when you join the TEACHERS table you have to deal with some ambiguous column names and add prefixes to distinguish them. To keep this from getting too verbose I've employed table aliases. I also converted to explicitly listed columns to eliminate the redundant ones:
SELECT STUDENT_ID, k.FIRST_NAME, k.LAST_NAME, k.GRADE , CLASS_ID, PERIOD_ID, TEACHER_ID , t.FIRST_NAME || ' ' || t.LAST_NAME AS TEACHER , ROOM_ID , SUBJECT_ID, NAME, DESCRIPTION FROM STUDENTS k JOIN SCHEDULE s ON k.ID = s.STUDENT_ID JOIN CLASSES c ON c.ID = s.CLASS_ID JOIN TEACHERS t ON t.ID = c.TEACHER_ID JOIN SUBJECTS u ON u.ID = c.SUBJECT_ID WHERE k.FIRST_NAME = 'Rex' AND k.LAST_NAME = 'Rios' ORDER BY PERIOD_ID ASC
Paul Brubaker9,925 Points
Here's how I did it.
SELECT SUBJECTS.NAME AS subject, ROOMS.ID AS room, TEACHERS.FIRST_NAME || " " || TEACHERS.LAST_NAME AS teacher, PERIODS.ID as period, PERIODS.START_TIME AS begins FROM STUDENTS JOIN SCHEDULE ON SCHEDULE.STUDENT_ID = STUDENTS.ID JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID JOIN TEACHERS ON TEACHERS.ID = CLASSES.TEACHER_ID, PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID, ROOMS ON ROOMS.ID = CLASSES.ROOM_ID, SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID WHERE STUDENTS.FIRST_NAME || " " || STUDENTS.LAST_NAME = "Rex Rios" ORDER BY PERIODS.ID;
This is how I managed to add teacher names to the schedule. Hope this helps:
-- Generate a schedule for Rex Rios. SELECT SUBJECTS.NAME AS "Class" , CLASSES.PERIOD_ID AS "Period" , TEACHERS.LAST_NAME AS "Teacher" , CLASSES.ROOM_ID AS "Room No." FROM CLASSES INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID INNER JOIN TEACHERS ON CLASSES.TEACHER_ID = TEACHERS.ID WHERE CLASSES.ID IN ( SELECT SCHEDULE.CLASS_ID FROM SCHEDULE WHERE STUDENT_ID IN ( SELECT ID FROM STUDENTS WHERE STUDENTS.FIRST_NAME = "Rex" AND STUDENTS.LAST_NAME = "Rios" ) ) ORDER BY PERIOD_ID ASC;