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
Stephanie Marek
2,831 PointsSyntax Error Help!
This is basically identical to the video, but is returning a syntax error at
), WITH SEVEN_ART AS (
What is the problem?
WITH FIVE_SCIENCE AS ( SELECT ST.ID FROM CLASSES AS C JOIN SUBJECTS AS S ON S.ID = C.SUBJECT_ID JOIN SCHEDULE AS SC ON SC.CLASS_ID = C.ID JOIN STUDENTS AS ST ON ST.ID = SC.STUDENT_ID WHERE C.PERIOD_ID = 5 AND S.NAME = "Science" ), WITH SEVEN_ART AS ( SELECT ST.ID FROM CLASSES AS C JOIN SUBJECTS AS S ON S.ID = C.SUBJECT_ID JOIN SCHEDULE AS SC ON SC.CLASS_ID = C.ID JOIN STUDENTS AS ST ON ST.ID = SC.STUDENT_ID WHERE C.PERIOD_ID = 7 AND S.NAME = "Art" )
SELECT ST.ID FROM FIVE_SCIENCE AS FIVE INNER JOIN SEVEN_ART AS SEVEN ON FIVE.STUDENT_ID = SEVEN.STUDENT_ID;
2 Answers
Stuart Wright
41,120 PointsI think the problem is that you should only write the WITH keyword once, even when defining multiple common table expressions.
This is correct:
WITH table_1 as (
-- insert query here
),
table_2 as (
-- insert query here
)
SELECT...
This is incorrect:
WITH table_1 as (
-- insert query here
),
WITH table_2 as (
-- insert query here
)
SELECT...
Stuart Wright
41,120 PointsLooks like your FIVE_SCIENCE table doesn't have a STUDENT_ID column, but rather simply an ID column?
Stephanie Marek
2,831 PointsStephanie Marek
2,831 PointsHi Stuart,
That worked, thanks!
Now I have an issue with my join of of the two tables. It's telling me the FIVE.STUDENT_ID column I'm trying to join doesn't exist.
WITH FIVE_SCIENCE AS ( SELECT ST.ID FROM CLASSES AS C JOIN SUBJECTS AS S ON S.ID = C.SUBJECT_ID JOIN SCHEDULE AS SC ON SC.CLASS_ID = C.ID JOIN STUDENTS AS ST ON ST.ID = SC.STUDENT_ID WHERE C.PERIOD_ID = 5 AND S.NAME = "Science" ), SEVEN_ART AS ( SELECT ST.ID FROM CLASSES AS C JOIN SUBJECTS AS S ON S.ID = C.SUBJECT_ID JOIN SCHEDULE AS SC ON SC.CLASS_ID = C.ID JOIN STUDENTS AS ST ON ST.ID = SC.STUDENT_ID WHERE C.PERIOD_ID = 7 AND S.NAME = "Art" )
SELECT * FROM FIVE_SCIENCE AS FIVE INNER JOIN SEVEN_ART AS SEVEN ON FIVE.STUDENT_ID = SEVEN.STUDENT_ID;
Any thoughts?