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

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
218,596 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

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
218,596 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

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
218,596 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.