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 trialIkechukwu Arum
5,337 PointsERROR: NO SUCH COLUMN
-- Which students have 5th period science and 7th period art?
WITH FirstQuery AS(
SELECT STUDENTS.ID FROM SUBJECTS
INNER JOIN CLASSES ON CLASSES.SUBJECT_ID = SUBJECTS.ID
INNER JOIN SCHEDULE ON SCHEDULE.CLASS_ID= CLASSES.ID
INNER JOIN STUDENTS ON STUDENTS.ID = SCHEDULE.STUDENT_ID
WHERE CLASSES.PERIOD_ID=5 AND SUBJECTS.name LIKE "science"
ORDER BY STUDENTS.ID
),
SecondQuery AS(
SELECT STUDENTS.ID FROM SUBJECTS
INNER JOIN CLASSES ON CLASSES.SUBJECT_ID = SUBJECTS.ID
INNER JOIN SCHEDULE ON SCHEDULE.CLASS_ID= CLASSES.ID
INNER JOIN STUDENTS ON STUDENTS.ID = SCHEDULE.STUDENT_ID
WHERE CLASSES.PERIOD_ID=7 AND SUBJECTS.name LIKE "art"
ORDER BY STUDENTS.ID
)
SELECT * FROM FirstQuery INNER JOIN SecondQuery ON FirstQuery.STUDENTS.ID = SecondQuery.STUDENTS.ID
I can't figure out why it won't recognize
FirstQuery.STUDENTS.ID
not sure what I am doing wrong here. Can anyone help me?
3 Answers
Steven Parker
231,184 PointsFrom outside of the CTE, that column can be referenced as "FirstQuery.ID
". The original source table is no longer part of the name.
Ikechukwu Arum
5,337 PointsGIVEN
WITH FirstQuery AS(
SELECT STUDENTS.ID FROM SUBJECTS
INNER JOIN CLASSES ON CLASSES.SUBJECT_ID = SUBJECTS.ID
INNER JOIN SCHEDULE ON SCHEDULE.CLASS_ID= CLASSES.ID
INNER JOIN STUDENTS ON STUDENTS.ID = SCHEDULE.STUDENT_ID
WHERE CLASSES.PERIOD_ID=5 AND SUBJECTS.name LIKE "science"
ORDER BY STUDENTS.ID
),
i'm not sure If
FirstQuery.ID
is referencing
STUDENTS.ID
or
SCHEDULE.STUDENT_ID
so I was thinking an alias like in
WITH FirstQuery AS(
SELECT STUDENTS.ID AS THISID FROM SUBJECTS
INNER JOIN CLASSES ON CLASSES.SUBJECT_ID = SUBJECTS.ID
INNER JOIN SCHEDULE ON SCHEDULE.CLASS_ID= CLASSES.ID
INNER JOIN STUDENTS ON STUDENTS.ID = SCHEDULE.STUDENT_ID
WHERE CLASSES.PERIOD_ID=5 AND SUBJECTS.name LIKE "science"
ORDER BY STUDENTS.ID
),
I can then reference the field I really want
SELECT * FROM FirstQuery.THISID INNER JOIN SecondQuery ON FirstQuery.STUDENTS.ID = SecondQuery.STUDENTS.ID
Steven Parker
231,184 PointsYou can be sure that "FirstQuery.ID" is referencing STUDENTS.ID ...
It would be "FirstQuery.STUDENT_ID" to reference SCHEDULE.STUDENT_ID.
But you know both are the same because that is the JOIN criterion.
Ikechukwu Arum
5,337 PointsIkechukwu Arum
5,337 Pointsthanks . I'm still trying to get use to the quirks in the syntax. looks like I'll have to put an alias if i want to continue writing it the way I wrote it.
Steven Parker
231,184 PointsSteven Parker
231,184 PointsI don't think you can use a period as part of an alias. But you could substitute an underscore.