MYSQL: Identify the five actors who share the greatest number of films with Nick Wahlberg (Sakila Database)

table 1: film_actor contains:

film_id,
actor_id

table 2: actor contains

actor_id,
first_name,
last_name

I have tried:

  select actor.id, actor.first_name, actor.last_name, count(*)
from actor
join film_actor am on actor.actor_id = film_actor.actor_id
where film_id in (select film_id from film_actor where actor_id = 'Nick Wahlberg')
group by actor.actor_id
order by count(*) desc;

I realize I am probably very off.

Answer

Breaking it down, you’d want to start with all the movies that nick starred in:

select film_id, actor_id
  from film_actor
  inner join actor using (actor_id)
  where first_name = 'nick'

The actor_id field will become important later

Subsequently, you’d want to find the actors that starred in that list of movies, but aren’t nick. For that you can join against an uncorrelated subquery:

select actor.actor_id, first_name
from actor
inner join film_actor using (actor_id)
inner join (
  select film_id, actor_id nick_actor_id
  from film_actor
  inner join actor using (actor_id)
  where first_name = 'nick'
) co_starring on film_actor.film_id = co_starring.film_id and actor.actor_id <> co_starring.nick_actor_id

Finally, you perform the grouping and ordering:

select actor.actor_id, first_name, count(*) as co_starred
from actor
inner join film_actor using (actor_id)
inner join (
  select film_id, actor_id nick_actor_id
  from film_actor
  inner join actor using (actor_id)
  where first_name = 'nick'
) co_starring on film_actor.film_id = co_starring.film_id and actor.actor_id <> co_starring.nick_actor_id
group by actor_id, first_name
order by co_starred desc;

Fiddle