Subjects in the Big Room2:13 with Ben Deitch
Which subject/s are taught in the largest room?
And also, which subjects are taught in that room? 0:00 For this query, we'll need to find a way to tie together the subjects table, and 0:04 the rooms table. 0:08 And to do that, we can use the classes table. 0:09 The CLASSES table has both the SUBJECT_ID column and a ROOM_ID column. 0:12 So, let's start by selecting everything from 0:17 the classes table WHERE the ROOM_ID is = to 19. 0:22 Then from here, we need to join to the subjects table, so 0:28 we can resolve these subjects into whatever subjects they actually are. 0:31 So to do this, we're going to join from, 0:36 CLASSES.SUBJECT_ID to SUBJECTS.ID. 0:40 So back in the query, let's add a JOIN, and let's join to SUBJECTS on 0:45 CLASSES.SUBJECT_ID = SUBJECTS.ID. 0:54 And if we run it now, We can see the subjects table pulling in over here. 1:02 So it looks like the two classes taught in room 19 are band and choir. 1:07 But how would we get this down to just two rows that say band and choir? 1:13 Well, for starters, let's not select everything. 1:17 Let's just select the NAME, and since there's only one NAME column, 1:21 this NAME is going to be from the subjects table. 1:25 And if we run this, we get band, band, band and choir, choir, choir. 1:28 And real quick, why we're getting six rows for 1:33 this, is just because there's three classes of each one. 1:36 One row for each class. 1:39 So, getting back to our names here, band, band, band, choir, choir, choir. 1:41 If we wanna get these down to two rows, we just need to use the distinct keyword, 1:46 which will return one row for each instance of a value and a column. 1:51 And now if we run it, we see that I spelled distinct wrong. 1:57 So let's add a C. 2:02 And then we get the coloring to let us know this is actually a keyword. 2:04 And we can run it, and 2:07 we get band and choir as the subjects that are taught in room 19. 2:09
You need to sign up for Treehouse in order to download course files.Sign up