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

Tommy Gebru
Tommy Gebru
30,151 Points

Solution: Finding the Troublemaker

-- Which students have 5th period science and 7th period art?

I tried following along with the video but ended up confused I can share my work below but for now I want to share a solution by Ryan Dainton and his use of the INTERSECT keyword

SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM CLASSES
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
  JOIN STUDENTS ON SCHEDULE.STUDENT_ID = STUDENTS.ID
  WHERE SUBJECTS.NAME = 'Science' AND PERIOD_ID = '5'

INTERSECT

SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM CLASSES
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
  JOIN STUDENTS ON SCHEDULE.STUDENT_ID = STUDENTS.ID
  WHERE SUBJECTS.NAME = 'Art' AND PERIOD_ID = '7';

1 Answer

Tommy Gebru
Tommy Gebru
30,151 Points

This is what I was struggling with before I decided to seek out solutions in the forum :point_left:

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

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

SELECT STUDENT_ID
FROM SCIENCE_PERIOD_FIVE AS SCI_FIVE
JOIN ART_PERIOD_SEVEN AS ART_SEVEN
ON SCI_FIVE.STUDENT_ID = ART_SEVEN.STUDENT_ID
JOIN