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.