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 Finding the Troublemaker

Stephanie Marek
Stephanie Marek
2,831 Points

Syntax 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
Stuart Wright
41,102 Points

I 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...
Stephanie Marek
Stephanie Marek
2,831 Points

Hi 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?

Stuart Wright
Stuart Wright
41,102 Points

Looks like your FIVE_SCIENCE table doesn't have a STUDENT_ID column, but rather simply an ID column?