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 2: Advanced Selecting A Smelly Issue

Stamos Bolovinos
Stamos Bolovinos
4,320 Points
Posted by Stamos Bolovinos
Stamos Bolovinos
4,320 Points

Why joining the students table?

There is no need to join the students table.

SELECT COUNT(*) 
FROM classes
JOIN schedule ON id = class_id
WHERE subject_id IN (SELECT id FROM subjects WHERE name = 'Physical Education')
AND period_id = 1;

will accomplish the same as the teachers example

SELECT COUNT(1) 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 = 1 AND name = 'Physical Education'

3 Answers

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

You could also just eliminate that table from the original example, and reduce it to:

SELECT COUNT(1) FROM schedule
JOIN classes ON classes.id = schedule.class_id
JOIN subjects ON subjects.id = classes.subject_id
WHERE period_id = 1 AND name = 'Physical Education'

I can think of one reason it might be desirable to do it the original way, in case the classes table potentially contained records for students who were no longer current, and you needed the students table to limit the count to only current students. But I agree it doesn't seem to be needed here.

If you feel this is a bug, you could report it to Support. I'm also tagging Ben Deitch in case he might like to comment directly.

Daniel Arnost
Daniel Arnost
7,190 Points
Daniel Arnost
Daniel Arnost
7,190 Points 
SELECT COUNT(*)
FROM STUDENTS
    INNER JOIN SCHEDULE
      ON STUDENTS.ID = SCHEDULE.STUDENT_ID
    INNER JOIN CLASSES 
      ON CLASSES.ID = SCHEDULE.CLASS_ID
WHERE CLASSES.SUBJECT_ID IN(19, 25, 31)  --phys ed 6th, 7th, and 8th
 /*WHERE SUBJECT_ID IN (
SELECT ID        
FROM SUBJECTS
 WHERE NAME = 'Physical Education')*/
 AND PERIOD_ID = 1;

So it should be obvious from reading the code you are counting students ( in case you or a co-worker wants to grab this from the repo and use this as a subquery ). Mostly wanted to share my answer :P

Paul Brubaker
Paul Brubaker
14,290 Points
Paul Brubaker
Paul Brubaker
14,290 Points

That's a nice catch. It probably just felt natural to start with the students table, since it is students being counted. I went about the task in the exact same way Ben did, line for line, also starting with the students table, and would never have noticed it wasn't necessary if no one had pointed it out. Maybe it's just our human bias at work; it makes more sense to us when we think of a list of people to start with the table that includes their names and not just their ID numbers. Not necessarily a bad thing, but I guess it cost us a little performance on this one.