Comparing values of columns from 3 tables - 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

Finally figured it out..

>SELECT m.name, m.year
-> FROM movies m
->JOIN roles r ON m.id = r.movie_id
->JOIN actors a ON a.id = r.actor_id
->WHERE a.first_name = "whatever actor's first name"
->AND a.last_name = "whatever actor's last name"

This will then give you two columns with the corresponding name and year! hazzah!

Answer 2

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.

Answer 3

You need a join. Try this:

SELECT A.first_name,A.last_name,A.gender,M.name,M.year,M.rank,R.role
FROM roles R INNER JOIN
Movies M ON R.movie_id = M.movie_id INNER JOIN
Actors A ON R.actory_id = A.id