1 00:00:00,200 --> 00:00:02,770 Well, that's certainly most of the teachers. 2 00:00:02,770 --> 00:00:04,470 Another thing they'd like to know is, 3 00:00:04,470 --> 00:00:07,710 are there any teachers that teach more than subject? 4 00:00:07,710 --> 00:00:10,690 And if so, which teachers, and which subjects do they teach? 5 00:00:11,860 --> 00:00:15,020 To figure out if any teachers teach multiple subjects, 6 00:00:15,020 --> 00:00:18,460 we need to join from the teachers table to the classes table. 7 00:00:18,460 --> 00:00:23,905 So, let's start by selecting everything from the teacher's table, 8 00:00:23,905 --> 00:00:30,180 and then joining to the classes table on TEACHERS.ID = CLASSES.TEACHER_ID. 9 00:00:30,180 --> 00:00:34,054 And since we're just trying to figure out if they teach multiple subjects, 10 00:00:34,054 --> 00:00:36,717 and we don't need to know which subjects those are, 11 00:00:36,717 --> 00:00:40,140 we don't actually need to join through to the subject table. 12 00:00:40,140 --> 00:00:43,640 We can just use the SUBJECT_ID from the classes table. 13 00:00:43,640 --> 00:00:50,584 So from here, we're going to need to group by teachers, GROUP BY TEACHER.ID, 14 00:00:50,584 --> 00:00:55,750 and then let's SELECT TEACHERS.* and then if we run this, 15 00:00:55,750 --> 00:01:01,140 that's kinda cool that this works, in case you didn't know. 16 00:01:02,360 --> 00:01:07,228 And, what we also wanna select is the COUNT of the SUBJECT_ID. 17 00:01:09,422 --> 00:01:12,830 But, this is going to count every SUBJECT_ID. 18 00:01:12,830 --> 00:01:15,710 We just wanna know if there's different subjects. 19 00:01:15,710 --> 00:01:21,870 So instead of counting SUBJECT_ID itself, let's count DISTINCT SUBJECT_ID. 20 00:01:21,870 --> 00:01:29,510 And if we run this, we've got some one's and we got a two for Janis Ambrose. 21 00:01:29,510 --> 00:01:33,870 So, just like in the last query, to get this to return only Janis, 22 00:01:33,870 --> 00:01:37,620 we're going to move this part into a having clause. 23 00:01:37,620 --> 00:01:42,680 So after our GROUP BY, let's add HAVING, and let's cut this out, 24 00:01:44,874 --> 00:01:48,901 And get rid of the comma, and then paste this down here. 25 00:01:48,901 --> 00:01:51,934 And, right, we need to have to count b something, and 26 00:01:51,934 --> 00:01:53,590 let's make greater than one. 27 00:01:53,590 --> 00:01:55,838 And there we go.