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

Posted by jun cheng wong
jun cheng wong
17,021 Points

NO SUCH COLUMN: A.STUDENT_ID WHEN I RUN THIS CODE

WITH FIFTH_SCIENCE AS (
  SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM STUDENTS
  INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
  INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
  INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
  WHERE CLASSES.PERIOD_ID = 5 AND SUBJECTS.NAME = "Science"
), SEVENTH_ART AS (
  SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM STUDENTS
  INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
  INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
  INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
  WHERE CLASSES.PERIOD_ID = 7 AND SUBJECTS.NAME = "Art"
)

SELECT * 
  FROM FIFTH_SCIENCE AS A 
  JOIN SEVENTH_ART AS B ON A.STUDENT_ID = B.STUDENT_ID
  JOIN STUDENTS AS C ON A.STUDENT_ID = C.ID

2 Answers

Peter Vann
Peter Vann
36,425 Points

Hi Jun!

Try this:

  WITH FIFTH_SCIENCE AS (
  SELECT STUDENT_ID
  FROM STUDENTS
  INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
  INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
  INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
  WHERE CLASSES.PERIOD_ID = 5 AND SUBJECTS.NAME = "Science"
), SEVENTH_ART AS (
  SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM STUDENTS
  INNER JOIN SCHEDULE ON STUDENTS.ID = SCHEDULE.STUDENT_ID
  INNER JOIN CLASSES ON CLASSES.ID = SCHEDULE.CLASS_ID
  INNER JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
  WHERE CLASSES.PERIOD_ID = 7 AND SUBJECTS.NAME = "Art"
)

SELECT C.* 
  FROM FIFTH_SCIENCE AS A 
  JOIN SEVENTH_ART AS B ON A.STUDENT_ID = B.ID
  JOIN STUDENTS AS C ON A.STUDENT_ID = C.ID

You had

SELECT C.* 
  FROM FIFTH_SCIENCE AS A 
  JOIN SEVENTH_ART AS B ON A.STUDENT_ID = B.STUDENT_ID
  JOIN STUDENTS AS C ON A.STUDENT_ID = C.ID

It should be B.ID

(I know it's different code from his example, but it worked for me. Sometimes, as in this case, I have to do a lot of trial-n-error until I figure stuff out and make it work.)

(BTW and for the record, I find SQL joins to be VERY confusing and usually a pain in the *ss!?! LOL)

I hope that helps.

Stay safe and happy coding!

jun cheng wong
jun cheng wong
17,021 Points
jun cheng wong
jun cheng wong
17,021 Points

Thanks for figuring out that for me. I find SQL fun even though it is quite confusing.