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

Tommy Gebru
Tommy Gebru
29,403 Points

Solution: JANIS Schedule

-- What class does Janis Ambrose teach during each period? Be sure to include all 7 periods in your report!

The teachers walkthrough solution which returns an error:

WITH JANIS_SCHEDULE AS (
SELECT PERIOD_ID, CLASSES.SUBJECT_ID FROM TEACHERS
JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE TEACHERS.ID = 391)
SELECT PERIODS.ID, JANIS_SCHEDULE.NAME FROM PERIODS
LEFT OUTER JOIN JANIS_SCHEDULE
ON PERIODS.ID = PERIOD_ID;

I played around with it some more and this is the line where the error is coming from

SELECT PERIODS.ID, JANIS_SCHEUDLE.NAME FROM PERIODS

instead the solution is

SELECT PERIODS.ID,  NAME FROM PERIODS

:point_up: also this will display a solution too :point_down:

SELECT ID,  NAME FROM PERIODS

no errors and solution is displayed when you omit the temporary table name :smile: :smile:

Tommy Gebru
Tommy Gebru
29,403 Points

ALSO there is another quick solution provided by Steven Parker without using the CTE or common table expression:

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