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

Databases SQL Reporting by Example Day 3: Getting Good at Grouping Finding the Troublemaker

Ikechukwu Arum
Ikechukwu Arum
5,337 Points
Posted by Ikechukwu Arum
Ikechukwu Arum
5,337 Points

ERROR: 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
Steven Parker
231,275 Points
Steven Parker
Steven Parker
231,275 Points

From 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
Ikechukwu Arum
5,337 Points
Ikechukwu Arum
Ikechukwu Arum
5,337 Points

thanks . 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
Steven Parker
231,275 Points
Steven Parker
Steven Parker
231,275 Points

I don't think you can use a period as part of an alias. But you could substitute an underscore.

Ikechukwu Arum
Ikechukwu Arum
5,337 Points
Ikechukwu Arum
Ikechukwu Arum
5,337 Points

GIVEN

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
Steven Parker
231,275 Points
Steven Parker
Steven Parker
231,275 Points

You 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.