SELECT TWO COLUMNS THAT HAVE THE SAME ID IN DIFFERENT TABLES AND MAKE COMPARISONS

Question

Good night Devs,

I need to take each actor_id and film_id and check which total of movies(filme_id) has linked to actor_id and this I got : SELECT actor_id, COUNT(film_id) from film_actor group by actor_id;

After that and what is my doubt now, is to take the first_name and actor_id of the actor table and compare with the ID of the film_actor table

I am using the Sakilla Database that comes with Mysql Workbench

• The teacher’s task: Select the first name (first_name) and the total number of films that each actor/actress (actor) is associated with(a) (use COUNT and GROUP BY). (0,3)

Answer 1

Solved using the code below = SELECT actor.first_name, Count(film_actor.actor_id) AS 'TOTAL' FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id GROUP BY film_actor.actor_id;