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 Busiest Teachers

Posted by seth aruby
seth aruby
10,111 Points

Making query account for unknown PERIODS count

Shouldn't this query be written such that it is PERIODS count agnostic? Yes, the comment in the example tells you there are 7 periods but it seems like it would be better to have within the query some sort of check on the PERIODS table for the actual count of PERIODS currently in the db?

seth aruby
seth aruby
10,111 Points

ie, something like this...

SELECT t.id, t.first_name, t.last_name, COUNT(c.period_id) AS period_count from Teachers AS t
JOIN Classes AS c ON c.teacher_id=t.id
GROUP BY t.id HAVING period_count = (SELECT COUNT(ID) FROM PERIODS);

1 Answer

joelearner
joelearner
54,915 Points
joelearner
joelearner
54,915 Points

Hi Seth,

Are you saying you want a way for the database to tell you how many periods are possible without the teacher telling you ahead of time? In that case, you can write a query to return the highest period number like this:

SELECT MAX(PERIOD_ID) AS HIGHEST_NUMBER FROM TEACHERS
JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID

Then you can change the query back to the way the teacher shows to retrieve the records matching the maximum period number.

Cheers!

seth aruby
seth aruby
10,111 Points

Hey Joe, Thanks for your help.

Yes that is what I was driving at. To me, the query should be more dynamic assuming first that the number of periods may change (say a school that is moving from a traditional schedule to a block schedule) and second that the individual writing the query will not have to update the query every time the school changes its schedule. I like what you have proposed using MAX() but I wonder if it is dynamic enough?? I'm thinking that doing a COUNT on the primary key column in the PERIODS table might be the best since even with MAX(PERIOD_ID) you could technically get into a situation where someone removed a PERIOD record between 1-7 and you'd really only have 7 periods but the MAX(PERIOD_ID) would return 8. I'm likely overthinking though.