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 trialspencer tintorri
6,184 PointsCTE 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
14,554 PointsPERIOD_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;