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 1: Joining Tables Subjects in the Big Room

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

A different approach and learning about JOIN

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.

Bruce McMinn
Bruce McMinn
10,030 Points
Bruce McMinn
Bruce McMinn
10,030 Points

You could let me know if I'm correct about the order of the tables. I was surprised to find out that a JOIN is the same as an INNER JOIN.

josephr
josephr
18,877 Points
josephr
josephr
18,877 Points

I imagine the best approach is context dependent. When I read the question my mindset was to answer it as directly as possible. I tried the code below, which gave me the correct answers and literally nothing else. In retrospect, displaying some of the other data seems like a good idea. 

SELECT * FROM subjects WHERE id IN 
(SELECT subject_id FROM classes 
WHERE 
room_id = 
(SELECT id FROM rooms WHERE capacity=
(SELECT MAX(capacity) FROM ROOMS)));

4 Answers

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

You're right, the standard "JOIN" is an "INNER JOIN". And since it is a symmetric operation, the order of the tables is not important.

However, and this may be just a personal preference, I like to put the tables in the order that elements from them first appear in the SELECT list.

rafael mari
rafael mari
4,500 Points
rafael mari
rafael mari
4,500 Points

I would add that using join here isn't necessary as a derived table seems to do the trick:

I used this code, and it appears to work

Select * from subjects where id in (select subject_id from classes where room_id=19)

Mark Chesney
Mark Chesney
11,747 Points
Mark Chesney
Mark Chesney
11,747 Points

I really like the workshop on Common Table Expressions. Here's my CTE that seems to work :)

WITH subjects_in_largest_room AS (
  SELECT DISTINCT SUBJECT_ID FROM CLASSES
  WHERE ROOM_ID IN (
    SELECT ID FROM ROOMS
    ORDER BY CAPACITY DESC
    LIMIT 1
  )
)
SELECT NAME FROM SUBJECTS
INNER JOIN subjects_in_largest_room
ON SUBJECTS.ID = subjects_in_largest_room.SUBJECT_ID;
Mark Chesney
Mark Chesney
11,747 Points

and here's just another variation I worked hard to figure out, using a MAX(CAPACITY) instead of LIMIT 1:

WITH subjects_in_largest_room AS (
  SELECT DISTINCT SUBJECT_ID FROM CLASSES
  WHERE ROOM_ID IN (
    SELECT ID FROM ROOMS
    WHERE CAPACITY = (
      SELECT MAX(CAPACITY) AS max_cap FROM ROOMS
    )
  )
)
SELECT NAME FROM SUBJECTS
INNER JOIN subjects_in_largest_room
ON SUBJECTS.ID = subjects_in_largest_room.SUBJECT_ID;
Sumiya Malik
Sumiya Malik
3,976 Points
Sumiya Malik
Sumiya Malik
3,976 Points 
SELECT DISTINCT SUBJECTS.ID AS SUBJECT_ID, SUBJECTS.NAME FROM SUBJECTS
JOIN CLASSES
ON SUBJECTS.ID= CLASSES.SUBJECT_ID
JOIN ROOMS
ON ROOMS.ID=CLASSES.ROOM_ID
WHERE ROOMS.capacity IN (SELECT MAX(capacity) FROM rooms)
Mia Filisch
Mia Filisch
16,117 Points
Mia Filisch
Mia Filisch
16,117 Points

I went down a similar route as Sumiya Malik in retaining the logic for possible multiple rooms with the same maximum capacity, but decided to live a little 🚀 with a double subquery for the condition - so I don't need the join on the rooms table as the subquery already returns the right room id(s).

SELECT DISTINCT s.name FROM subjects AS s
JOIN classes AS c ON s.id = c.subject_id
WHERE c.room_id IN (
  SELECT id FROM rooms
  WHERE capacity = (
    SELECT MAX(capacity) FROM rooms
  )
)