Databases SQL Reporting by Example Day 3: Getting Good at Grouping Janis' Schedule

elomaye
elomaye
5,221 Points

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;
Dave StSomeWhere
Dave StSomeWhere
19,786 Points

Please 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.

3 Answers

Steven Parker
Steven Parker
177,579 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
elomaye
elomaye
5,221 Points

I see. It didn't occur to me to start with the PERIODS table. Thanks for the explanation!

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?

all_7_periods.sql
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
only_6_periods.sql
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
Steven Parker
177,579 Points

The 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?

Yes, I had to read that twice, but that does make sense. Thank you kindly, Steven! :)