Databases SQL Reporting by Example Day 2: Advanced Selecting Student Schedule

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

3 Answers

Steven Parker
Steven Parker
203,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 Brubaker
Paul Brubaker
9,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;

Hi Charlie,

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;