Display that a condition is true based on the joining of multiple tables

Question

Background:

I would like to display all the people who have worked with the actor Ben Affleck.

Here is my data:

  People table
  
  id    name
 
  1     Ben Affleck
  2     Leonardio DiCaprio
  3     Matt Damon


  Movies table

  id   title
  1    Good Will Hunting
  2    example
  3    example


  Credits table
 
  id  movie_id person_id 
 
  1   1        1
  2   2        4
  3   3        6

Desired Output

 name         movie
 
 Matt Damon   Good Will Hunting
 

This is what I have tried:

I know that I must join the People, Movies, and Credits table together, as I am trying to find all the actors that have worked with Ben Affleck:

 SELECT p.name, m.title, c.movie_id
 FROM Credits c
 JOIN People p ON p.id = c.person_id
 JOIN Movies m ON m.id = p.id
 WHERE p.name = 'Ben Affleck';

I am still researching this.

Answer 1

Or you can use IN, which sometimes is less confusing to understand than EXISTS. You also had an error in joining Movies table in your initial query, you have to compare movie id from Movies which is id and movie id from Credits which is movie_id

SELECT p.name, m.title
FROM Credits c
JOIN People p ON p.id = c.person_id
JOIN Movies m ON m.id = c.movie_id
WHERE m.id IN (
  SELECT movie_id 
  FROM Credits c1
  JOIN People p1 on p1.id = c1.person_id
  WHERE p1.name = 'Ben Affleck'
)