比较3个表中列的值 - MySQL

Question

following question:

I'm working with 3 tables = actors, movies, roles. I'm trying to find all the movies a given actor, say 'Robin Williams' has been in by comparing the specific actor id since there be may more than one actor with the same name. The actors table has the following relevant columns: first_name, last_name, id - the movies table has columns: id (the movie's id) - and the roles table has: actor_id and movie_id.

Do I JOIN the tables or use UNION? How do I compare columns from different tables when the columns have different names?

Thank you!

Just for reference:

Table actors:

mysql> SELECT *
    -> FROM actors;
+--------+--------------------+------------------------+--------+------------+
| id     | first_name         | last_name              | gender | film_count |
+--------+--------------------+------------------------+--------+------------+
|    933 | Lewis              | Abernathy              | M      |          1 |
|   2547 | Andrew             | Adamson                | M      |          1 |
|   2700 | William            | Addy                   | M      |          1 |

Table movies:

mysql> SELECT *
    -> FROM movies;
+--------+------------------------------+------+------+
| id     | name                         | year | rank |
+--------+------------------------------+------+------+
|  10920 | Aliens                       | 1986 |  8.2 |
|  17173 | Animal House                 | 1978 |  7.5 |
|  18979 | Apollo 13                    | 1995 |  7.5 |

Table roles:

mysql> SELECT *
    -> FROM roles;
+----------+----------+-------------------------------+
| actor_id | movie_id | role                          |
+----------+----------+-------------------------------+
|    16844 |    10920 | Lydecker                      |
|    36641 |    10920 | Russ Jorden                   |
|    42278 |    10920 | Cpl. Dwayne Hicks             |

At first I tried setting each check equal to a PHP variable and comparing them but that seemed wrong, then I tried:

mysql> SELECT roles.actor_id, roles.movie_id, movies.id, actors.id
    -> FROM roles
    -> JOIN movies, actors
    -> ON roles.actor_id = actors.id && roles.movie_id =movies.id;

which again does not work.

Answer 1

First Read this answer - it made it click for me finally after years of unions when should join and vice versa.

In this case you should definitely JOIN as you want the result to act as a single row.

(think of it like this - I want to see Movie, Actor -> together as one result)

PS

You don't need your film count field any more as once you have the joins worked out you can just use MySQL COUNT -> it will make it easier to maintain.