Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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

Posted by Charlie Harcourt
Charlie Harcourt
8,046 Points

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
231,236 Points
Steven Parker
Steven Parker
231,236 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
14,290 Points
Paul Brubaker
Paul Brubaker
14,290 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;
Mark Chesney
Mark Chesney
11,747 Points
Mark Chesney
Mark Chesney
11,747 Points

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;