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

Bruce McMinn
Bruce McMinn
10,030 Points
Posted by Bruce McMinn
Bruce McMinn
10,030 Points

A 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
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

I 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
Bruce McMinn
10,030 Points
Bruce McMinn
Bruce McMinn
10,030 Points

You are correct. That is interesting. I'm moving this question. Thanks Steven.