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

Tommy Gebru
Tommy Gebru
30,164 Points
Posted by Tommy Gebru
Tommy Gebru
30,164 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,164 Points
Tommy Gebru
Tommy Gebru
30,164 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