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 3: Getting Good at Grouping Janis' Schedule

spencer tintorri
spencer tintorri
6,184 Points
Posted by spencer tintorri
spencer tintorri
6,184 Points

CTE reference question

The presented solution is

WITH JANIS_CLASSES AS (
  SELECT CLASSES.PERIOD_ID, SUBJECTS.NAME 
  FROM TEACHERS
  JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  WHERE TEACHERS.ID = 391
)

SELECT * FROM PERIODS
LEFT OUTER JOIN JANIS_CLASSES ON PERIODS.ID = PERIOD_ID;

period_id in the left outer join doesnt reference a table.

Why does the below table reference fail? - classes.period_id in the left outer join

WITH JANIS_CLASSES AS (
  SELECT CLASSES.PERIOD_ID, SUBJECTS.NAME 
  FROM TEACHERS
  JOIN CLASSES ON CLASSES.TEACHER_ID = TEACHERS.ID
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  WHERE TEACHERS.ID = 391
)

SELECT * FROM PERIODS
LEFT OUTER JOIN JANIS_CLASSES ON PERIODS.ID = CLASSES.PERIOD_ID;

Wouldn't this be a problem if period_id was in more than one table?

And why can't the left outer join recognize the table reference, when JANIS_CLASSES is being joined?

1 Answer

Jurgen de Vries
Jurgen de Vries
14,554 Points
Jurgen de Vries
Jurgen de Vries
14,554 Points

PERIOD_ID references JANIS_CLASSES table which you created as a sort of temporary table that only exists during your query.

Therefor you can leaf PERIOD_ID without a table reference in your first example. It is the only table in your select statement that has this column. You are only selecting from PERIODS and from JANIS_CLASSES.

That's also the reason your second example fails. You are not doing a direct select on the CLASSES table, but on the temporary JANIS_CLASSES table.

You could do this: SELECT * FROM PERIODS LEFT OUTER JOIN JANIS_CLASSES ON PERIODS.ID = JANIS_CLASSES.PERIOD_ID;