The Movie Database Solution: Most Keywords

[00:00:00]
>> We have the Movie Keywords table, we have the Categories table. So you have to start from movies, then go to movie keywords, which is a connecting table to categories. So you have to go from movies, to movie keywords, to categories. So we're gonna say SELECT m.name, movies.name.

[00:00:26]
And then we're gonna do a COUNT( c.id) from categories AS COUNT. So, this is gonna be how many different categories are associated with one movie, right? We're gonna start from Movies m. There's other ways to write this query by the way. This is not the only way you could do it, just the way that it occurred to my brain.

[00:00:53]
INNER JOIN Movie_keywords mk. ON mk.movie-id = m.id INNER JOIN categories On. Mk.category_id = c.id. So movie category id equals the category id. Good, so far we're gonna GROUP BY m.id, and m.name. ORDER BY Count descending, there should be no nulls in here, right? So I don't have to do the nulls last because the count is always gonna return a number.

[00:02:00]
And you can limit here by however you many want. Let's do 15. Let's check to make sure that works. So this is what I I'm getting for movies that have the most keywords. I look some of these up. This is, I think it said, Croatian or according to Google Translate.

[00:02:31]
This one was, what we decided, was Hindi, right? And a bunch of these came back as Hindi. Mogambo came back as Basque, which I'm gonna guess may not be correct. It could be Basque. Three Idiots, I'm guessing that's English. I don't know what that is, anyone know what that is?

[00:02:57]
Kind of look cool script.
>> It looks like Hebrew.
>> I don't think that's Hebrew. Anyway, those are the movies that have the most categories associated with them. So, if this was my database and I was running a website with it, I would look at this and say, does that one Croatian film really need 200 categories?

[00:03:19]
That's probably more than a category a minute. [LAUGH] I have a strict rule that you cannot have more than a category a minute.