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

Ikechukwu Arum
Ikechukwu Arum
3,495 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
177,579 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
3,495 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
177,579 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
3,495 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
177,579 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.