I have three tables: films, actors and the relationship actors_film.
I'm trying to decide which actors has been in the most number of movies. If the actor has been in a movie this is shown with actor_id(primary key).
I figured I want to count the number of times each actor_id shows in the table film_actor for each film_id, and then connect this with the right name(actor.first_name).
I've got the first part down, but can't seem to connect the Count-value to the right name.
SELECT actor_id, COUNT(*) AS number_of_films
FROM film_actor
GROUP by actor_id
ORDER by 2
DESC limit 1;
How would I go forward connecting the actors name from the actor-table?
I'm thinking I need some form of:
WHERE actor.actor_id = film_actor.actor_id.
You need a simple inner join between the table actors and the table actors_film:
SELECT a.actor_name, a.actor_id, COUNT(*)
FROM actors a INNER JOIN actors_films af
ON a.actor_id = af.actor_id
GROUP BY a.actor_name, a.actor_id
ORDER BY 3;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments