1 00:00:00,220 --> 00:00:03,130 And also, which subjects are taught in that room? 2 00:00:04,320 --> 00:00:08,190 For this query, we'll need to find a way to tie together the subjects table, and 3 00:00:08,190 --> 00:00:09,540 the rooms table. 4 00:00:09,540 --> 00:00:12,310 And to do that, we can use the classes table. 5 00:00:12,310 --> 00:00:17,550 The CLASSES table has both the SUBJECT_ID column and a ROOM_ID column. 6 00:00:17,550 --> 00:00:22,060 So, let's start by selecting everything from 7 00:00:22,060 --> 00:00:26,916 the classes table WHERE the ROOM_ID is = to 19. 8 00:00:28,899 --> 00:00:31,923 Then from here, we need to join to the subjects table, so 9 00:00:31,923 --> 00:00:36,810 we can resolve these subjects into whatever subjects they actually are. 10 00:00:36,810 --> 00:00:40,614 So to do this, we're going to join from, 11 00:00:40,614 --> 00:00:45,390 CLASSES.SUBJECT_ID to SUBJECTS.ID. 12 00:00:45,390 --> 00:00:52,193 So back in the query, let's add a JOIN, and let's join to SUBJECTS on 13 00:00:54,495 --> 00:01:02,060 CLASSES.SUBJECT_ID = SUBJECTS.ID. 14 00:01:02,060 --> 00:01:07,920 And if we run it now, We can see the subjects table pulling in over here. 15 00:01:07,920 --> 00:01:12,110 So it looks like the two classes taught in room 19 are band and choir. 16 00:01:13,560 --> 00:01:17,750 But how would we get this down to just two rows that say band and choir? 17 00:01:17,750 --> 00:01:20,340 Well, for starters, let's not select everything. 18 00:01:21,430 --> 00:01:25,280 Let's just select the NAME, and since there's only one NAME column, 19 00:01:25,280 --> 00:01:28,060 this NAME is going to be from the subjects table. 20 00:01:28,060 --> 00:01:33,230 And if we run this, we get band, band, band and choir, choir, choir. 21 00:01:33,230 --> 00:01:36,000 And real quick, why we're getting six rows for 22 00:01:36,000 --> 00:01:39,470 this, is just because there's three classes of each one. 23 00:01:39,470 --> 00:01:41,510 One row for each class. 24 00:01:41,510 --> 00:01:46,710 So, getting back to our names here, band, band, band, choir, choir, choir. 25 00:01:46,710 --> 00:01:51,969 If we wanna get these down to two rows, we just need to use the distinct keyword, 26 00:01:51,969 --> 00:01:57,170 which will return one row for each instance of a value and a column. 27 00:01:57,170 --> 00:02:00,940 And now if we run it, we see that I spelled distinct wrong. 28 00:02:02,010 --> 00:02:03,560 So let's add a C. 29 00:02:04,760 --> 00:02:07,880 And then we get the coloring to let us know this is actually a keyword. 30 00:02:07,880 --> 00:02:09,290 And we can run it, and 31 00:02:09,290 --> 00:02:13,559 we get band and choir as the subjects that are taught in room 19.