How 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;
Steven Parker200,541 Points
You'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
Hi 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
Yes, I had to read that twice, but that does make sense. Thank you kindly, Steven! :)