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 trialBruce McMinn
10,030 PointsA 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.
josephr
18,877 PointsI 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
231,184 PointsYou'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
4,500 PointsI 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
11,747 PointsI 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
11,747 Pointsand 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
3,976 PointsSELECT 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
16,117 PointsI 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
)
)
Bruce McMinn
10,030 PointsBruce McMinn
10,030 PointsYou 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.