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 trialjun cheng wong
17,021 PointsNO SUCH COLUMN: A.STUDENT_ID WHEN I RUN THIS CODE
WITH FIFTH_SCIENCE AS (
SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
FROM STUDENTS
INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE CLASSES.PERIOD_ID = 5 AND SUBJECTS.NAME = "Science"
), SEVENTH_ART AS (
SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
FROM STUDENTS
INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE CLASSES.PERIOD_ID = 7 AND SUBJECTS.NAME = "Art"
)
SELECT *
FROM FIFTH_SCIENCE AS A
JOIN SEVENTH_ART AS B ON A.STUDENT_ID = B.STUDENT_ID
JOIN STUDENTS AS C ON A.STUDENT_ID = C.ID
2 Answers
Peter Vann
36,427 PointsHi Jun!
Try this:
WITH FIFTH_SCIENCE AS (
SELECT STUDENT_ID
FROM STUDENTS
INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE CLASSES.PERIOD_ID = 5 AND SUBJECTS.NAME = "Science"
), SEVENTH_ART AS (
SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
FROM STUDENTS
INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
WHERE CLASSES.PERIOD_ID = 7 AND SUBJECTS.NAME = "Art"
)
SELECT C.*
FROM FIFTH_SCIENCE AS A
JOIN SEVENTH_ART AS B ON A.STUDENT_ID = B.ID
JOIN STUDENTS AS C ON A.STUDENT_ID = C.ID
You had
SELECT C.*
FROM FIFTH_SCIENCE AS A
JOIN SEVENTH_ART AS B ON A.STUDENT_ID = B.STUDENT_ID
JOIN STUDENTS AS C ON A.STUDENT_ID = C.ID
It should be B.ID
(I know it's different code from his example, but it worked for me. Sometimes, as in this case, I have to do a lot of trial-n-error until I figure stuff out and make it work.)
(BTW and for the record, I find SQL joins to be VERY confusing and usually a pain in the *ss!?! LOL)
I hope that helps.
Stay safe and happy coding!
jun cheng wong
17,021 PointsThanks for figuring out that for me. I find SQL fun even though it is quite confusing.