Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

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

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

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;