Multi-Subject Teachers1:57 with Ben Deitch
Do any teachers teach multiple subjects? If so, who and which subjects?
Well, that's certainly most of the teachers. 0:00 Another thing they'd like to know is, 0:02 are there any teachers that teach more than subject? 0:04 And if so, which teachers, and which subjects do they teach? 0:07 To figure out if any teachers teach multiple subjects, 0:11 we need to join from the teachers table to the classes table. 0:15 So, let's start by selecting everything from the teacher's table, 0:18 and then joining to the classes table on TEACHERS.ID = CLASSES.TEACHER_ID. 0:23 And since we're just trying to figure out if they teach multiple subjects, 0:30 and we don't need to know which subjects those are, 0:34 we don't actually need to join through to the subject table. 0:36 We can just use the SUBJECT_ID from the classes table. 0:40 So from here, we're going to need to group by teachers, GROUP BY TEACHER.ID, 0:43 and then let's SELECT TEACHERS.* and then if we run this, 0:50 that's kinda cool that this works, in case you didn't know. 0:55 And, what we also wanna select is the COUNT of the SUBJECT_ID. 1:02 But, this is going to count every SUBJECT_ID. 1:09 We just wanna know if there's different subjects. 1:12 So instead of counting SUBJECT_ID itself, let's count DISTINCT SUBJECT_ID. 1:15 And if we run this, we've got some one's and we got a two for Janis Ambrose. 1:21 So, just like in the last query, to get this to return only Janis, 1:29 we're going to move this part into a having clause. 1:33 So after our GROUP BY, let's add HAVING, and let's cut this out, 1:37 And get rid of the comma, and then paste this down here. 1:44 And, right, we need to have to count b something, and 1:48 let's make greater than one. 1:51 And there we go. 1:53
You need to sign up for Treehouse in order to download course files.Sign up