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

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

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!

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