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
Bruce McMinn
10,030 PointsA different approach to Databases/SQL Reporting By Example/Day 1:Joining Tables/Subjects in the Big Room
Initially I tried a query with two INNER JOINs that solved all three questions and resulted in a table you could print out and give the fire marshall.
--first block
SELECT rooms.id AS 'Room #', rooms.capacity, subjects.name AS 'Subject' FROM subjects
INNER JOIN classes ON classes.subject_id = subjects.id
INNER JOIN rooms ON classes.room_id = rooms.id
WHERE rooms.id =
(SELECT id FROM rooms WHERE rooms.capacity =
(SELECT MAX(rooms.capacity) FROM rooms))
GROUP BY subjects.name;
Then I watched the video and modified Ben's code.
--second block
SELECT rooms.id AS 'Room #', rooms.capacity, subjects.name AS 'Subject' FROM classes
JOIN subjects ON classes.subject_id = subjects.id
JOIN rooms ON classes.room_id = rooms.id
WHERE capacity IN (SELECT MAX(capacity) FROM rooms)
GROUP BY name;
And then I realized that if I just switch the WHERE clause on the first one, that it's basically the same thing.
--first block with edits
SELECT rooms.id AS 'Room #', rooms.capacity, subjects.name AS 'Subject' FROM subjects
JOIN classes ON classes.subject_id = subjects.id
JOIN rooms ON classes.room_id = rooms.id
WHERE capacity IN (SELECT MAX(capacity) FROM rooms)
GROUP BY subjects.name;
I guess since it's an INNER JOIN the order doesn't matter, but if we did an OUTER it would.
Also, how do I get a question into the specific section of the video that it refers to? Like, not just Databases, but Databases/SQL Reporting By Example/Day 1:Joining Tables/Subjects in the Big Room?
2 Answers
Steven Parker
231,248 PointsI believe you're asking about setting the "breadcrumbs", and that happens when you enter a question using the "Get Help" button in a challenge or in the "Questions?" tab on a video.
Bruce McMinn
10,030 PointsYou are correct. That is interesting. I'm moving this question. Thanks Steven.