Preview
Video Player
00:00
00:00
Multi-Subject Teachers
1:57Do 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