-: Hi guys, and welcome back.
In this video we're going to start learning about JOINs
and we're going to write one here in DB Browser,
and then go over to our editor and write it there
so we can extract movie title when we're showing the
watched movie information.
So, let's get started.
Here we've got DB Browser and as you can see,
we've got four movies in there at the moment.
Then in the users table we've got two users,
Jose and Rolf, and finally in the watched table
we've got nothing there at the moment.
We're gonna start off by adding a new record,
and here we're gonna put, for example,
Jose and watched movie one.
Then a new record, and also Jose and watched movie,
for example, three, let's say.
So, we've got The Matrix and Gone Girl,
and we're gonna make Rolf watch Mulan with id five,
so we'll add the new record there
and we've got Rolf and movie five.
So, there we go.
We've got our data and I know that you guys
already know how to do this with SQL,
that's why I'm just doing it like this,
but of course you could run the insert commands yourself
if you wanted to, that'd be totally fine.
So, now let's go over to execute SQL
and here is where we're gonna write our JOIN.
So, we're gonna start with a SELECT *
and remember that when we perform a JOIN
we can select columns from an individual table
or we can select individual columns,
we're gonna start with everything for now
just so we can see what the JOIN is producing.
Then we're gonna do FROM movies
and then we're gonna JOIN on watched, for example.
Now, what links movies to the watched table is the movie_id
in the movies table and the movie_id column
in the watched table.
So we'll do ON movies.id = watched.movie_id.
Okay, so we can start with this, we can run it,
and then you'll see, if I bring this down here,
that we have ourselves some information coming back.
We've got the data from the movies table,
the columns from the movies table,
id, title and release_timestamp,
then we've joined so that we get everything here
that has something from the right,
and so we are then matching over and getting the username
and the movie_id of course, which should match.
Now, the thing is, this here evaluates, essentially,
to this table down here.
So now we can JOIN the result with something else.
We can go and JOIN that
ON users.username = watched.user_username.
So, we're selecting FROM movies,
that is the first level of results.
Then we are essentially joining to it the watched table
we're appending that at the end,
and finally we're appending at the end of that
the users table where this matches.
So, we can press play again,
and now you can see that we get
the username there at the back.
Of course, this is not all that useful,
like we've said a few times,
because we've got the username there
that matches the foreign key exactly,
but you could have more useful information here,
more columns, we just decided not to put them in
because it's not really necessary for the example.
Okay, finally, what we can do is we can filter
on any of the columns that we have selected,
so we can filter on movie id, title, release_timestamp,
we can filter on watched user_username or watched movie_id
or we can filter on users.username.
And that's what we're gonna do here,
we're gonna say users.username =
and we're gonna say, for example, Jose.
Now, we just get the information coming back
for the user Jose,
we can replace that for anything else if we want.
So, in our code, that is gonna be the question mark.
All right, so let's copy that,
and go over to the code and we can use it in there.
By the way, if you're confused at this point
because of this query, don't worry too much,
this is by far the most complicated query we've written
so hopefully it makes some sense,
but if it's very confusing,
try to write a few more queries that join different tables,
maybe try a different example that's not movies,
for example, I don't know, books or something like that,
or your power tools from your shed,
whatever you like as an example, try that out,
as doing more examples is going to greatly help
in learning these things.
It's gonna make it click much more easily.
So, then query lets us get the movie data
of movies that a certain user has watched.
So we can go ahead and make some changes
over in database.py.
SELECT_WATCHED_MOVIES, at the moment,
is selecting the columns from the watched table,
but of course that doesn't return the information
that it wants.
So, if we simply replace that for our new query,
then we're going to be selecting, essentially,
and I'm gonna bring this over to this line,
we're selecting everything from the movies table,
the watched table and the users table
and at the end of the day what we do end up with,
as we saw in DB Browser is the information
related to movies that a particular user has watched,
as well as a bunch of other information
that we may not think is relevant.
Because this query is in charge,
or has been in charge up to now,
of selecting movie data that has been watched,
maybe instead of selecting * we wanna do movies.*.
And if we do this,
what we're gonna get back out of this entire query
is movie data that a user has watched.
So, again, if we go back to DB Browser
and I do movies.* you'll see that what we'll get back
is the movie columns that have been watched by Jose.
So, if I press play, then of course I have to
put in this string in here and we get that movie data now.
So, there we go, there we've got it.
So, of course we do want to keep
the question mark down there
because we're gonna be passing an argument in our code.
Remember that in app.py
we have a print_watched_movie_list function
that displays the movie information that a user has watched,
but this function was specifically made for stage two,
where movies were in their own table
alongside the username that watched them.
Now, get_watched_movies is returning movie columns,
exactly the same thing that, for example,
get_movies is returning.
Therefore, we no longer need print_watched_movie list,
now we can simply use the print_movie_list
that we have already.
So, we're gonna delete that function,
then coming back down over here to the menu,
in this section here we're gonna
make a couple of changes as well.
So, we want to get the username because
we need that for getting the watched movies,
and then we simply pass the movies to print_movie_list
and here you can do something like username
and then what's gonna happen is that is
gonna be passed over to this heading
and it will show, for example, Jose movies,
you can do something a bit fancier like something like this,
with the curly braces there for the up string,
watched movies, for example, like that,
or you can just do what we're gonna do
which is just put watched movies.
After all, they've just entered the username,
so they're gonna know who these watched movies are for,
we can just put watched movies,
that's gonna go over into the heading
and show watched movies.
Again, feel free to change things around if you want,
you don't want to put "watched" in there,
you can put something else in there if you want
and that's totally fine.
Now, something important is that sometimes users
are not gonna have any watched movies,
and so when we get this, that's gonna be an empty list.
We can do something like this,
if movies then print the movie list
otherwise, print something like,
"That user has no watched movies."
Or if you want to write something in slightly better English
you can say "Has watched no movies yet!"
Something like that.
Again, these strings are totally up to you.
You can do this same thing with these
other branches as well if you want,
that's gonna make your programme a little bit nicer
but it would all be quite similar
so we're not gonna do that here.
I'd say that this branch of code down here
is getting a little bit long,
so I'm going to cut it and put it into a new function,
so we're gonna do prompt_show_watched_movies
and we're gonna put everything in there,
remembering the indentation has to be correct
and then call that there.
All right, that's about it for this video.
I hope you're still with me,
even if a little bit confused,
remember to try different examples if you can,
build a couple apps that use a similar structure
to what we've got here,
and that's really gonna help cement this knowledge.
Thank you guys for watching, thanks for joining me
in this video, and I'll see you in the next one.