Stamos Bolovinos4,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'
Steven Parker203,694 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.
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 Brubaker9,925 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.