**Heads up!** To view this whole video, sign in with your Courses account or enroll in your free 7-day trial.
Sign In
Enroll

Preview

Start a free Courses trial

to watch this video

Which teachers don't have a class during 1st period?

Wow, that must keep her pretty busy.
0:00

The last thing I'd like you to figure out,
0:02

is which teachers don't
have a first period class?
0:04

They're having trouble unloading
the buses on time, and
0:07

are wondering if they can get
some of the teachers to help out.
0:10

To figure out which teachers don't
have a class during first period,
0:13

we can actually do
something pretty clever.
0:17

Instead of trying to solve
this question directly,
0:19

we can figure out which teachers do
have a class during first period, and
0:22

just subtract those teachers away
from all the other teachers.
0:26

So let's start by figuring out,
0:30

which teachers do have
a class during first period.
0:31

So let's SELECT* FROM TEACHERS, And
then to get the period information,
0:34

we'll need to join to the classes table.
0:39

So JOIN CLASSES ON TEACHERS.ID
0:41

= CLASSES.TEACHER_ID.
0:46

And let's add a WHERE clause.
0:50

And if we look at the CLASSES table,
we've got a PERIOD_ID column.
0:52

And if you don't have a CLASSES table
over here, you can always just select
0:57

star from classes or
whatever table you need to look into.
1:00

So WHERE PERIOD.ID = 1.
1:03

All right, here's the teachers
that have a class first period.
1:08

Now, all we need to do, is take
a selection of all the teachers, and
1:13

subtract away these teachers.
1:17

So above this,
let's SELECT* FROM TEACHERS.
1:19

And then, between these two queries,
all we need to write is EXCEPT,
1:25

which in some languages
will be the minus keyword.
1:29

Now, if we run the query, we get an error.
1:33

When you're using except, you need to
make sure that you have exactly the same
1:37

columns in your first query and
your second query.
1:41

So instead of selecting
everything from both of these,
1:44

since the second one
includes the classes table,
1:48

let's go ahead and
just select TEACHERS.* and TEACHERS.*.
1:52

And now if we run it, it looks like
we've only got one teacher back,
1:57

Cassandra McGuire.
2:01

Great work figuring out
all of these queries.
2:03

I'm not sure where they'd be without you.
2:06

On that note,
if you've got any questions or
2:08

comments about what you've seen here,
be sure to post about it in the community.
2:11

Until next time.
2:15

You need to sign up for Treehouse in order to download course files.

Sign up