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
elomaur
5,276 PointsHow can I show all periods without using a CTE?
I was unable to retrieve the 4th period with this query. I understand why it is better to use a CTE but I am curious about how I can retrieve the 4th period without using a CTE.
My query for this exercise was as follows:
SELECT PERIOD_ID,FIRST_NAME, LAST_NAME FROM TEACHERS
JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
LEFT OUTER JOIN PERIODS ON PERIODS.ID = CLASSES.PERIOD_ID
WHERE TEACHERS.ID = 391;
3 Answers
Steven Parker
231,269 PointsYou've got the right idea, but Janis (teacher 391) just doesn't have anything during 4th period.
So to be sure to display the 4th period, even with no class taught, start with PERIODS and LEFT JOIN the other tables. Then, by using the teacher's ID as part of the JOIN criteria instead of as a filter (WHERE), you can preserve any periods the teacher doesn't have a class in:
SELECT PERIODS.ID as 'Period',
SUBJECTS.NAME as 'Subject Janis Teaches'
FROM PERIODS
LEFT JOIN CLASSES ON PERIODS.ID = CLASSES.PERIOD_ID AND CLASSES.TEACHER_ID = 391
LEFT JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
elomaur
5,276 PointsI see. It didn't occur to me to start with the PERIODS table. Thanks for the explanation!
Mark Chesney
11,747 PointsHi Steven, I think it's fascinating and puzzling that there's a difference between the two queries below. I've never been taught to add an AND to a JOIN. Any quick tips behind this?
SELECT PERIODS.ID
, SUBJECTS.NAME
FROM PERIODS
LEFT JOIN CLASSES ON PERIODS.ID = PERIOD_ID AND TEACHER_ID = 391
LEFT JOIN SUBJECTS ON SUBJECTS.ID = SUBJECT_ID
SELECT PERIODS.ID
, SUBJECTS.NAME
FROM PERIODS
LEFT JOIN CLASSES ON PERIODS.ID = PERIOD_ID
LEFT JOIN SUBJECTS ON SUBJECTS.ID = SUBJECT_ID
WHERE TEACHER_ID = 391
Thank you!
Steven Parker
231,269 PointsThe WHERE clause filters the entire result, so it removes all rows that don't have a TEACHER_ID of 391 (Janis). So you're left with just the 6 periods where Janis teaches a class.
But in my suggested alternative, the requirement for the TEACHER_ID to be 391 is added as an extra join criterion along with the periods matching between the tables, so only classes taught by Janis are joined with the periods. This way, you don't need any filtering to remove joined rows with other teachers. But since it is an outer join (LEFT JOIN is short for LEFT OUTER JOIN), all rows from the PERIODS table are included whether or not they get joined with any CLASSES row. And so the final result has the rows for all 7 periods but one of them has no class (or teacher) info.
Does that make sense?
Mark Chesney
11,747 PointsYes, I had to read that twice, but that does make sense. Thank you kindly, Steven! :)
Dave StSomeWhere
19,870 PointsDave StSomeWhere
19,870 PointsPlease show the tables (structure and data), or a subset of the table rows that show the issue you are having. The please show your current output and desired output and we can then work out the query that provides your desired result.