Code
.
.
+------------+-----------+--------+------------------------------+--------+----------+----------+
| first_name | last_name | id | name | id | actor_id | movie_id |
+------------+-----------+--------+------------------------------+--------+----------+----------+
| Kevin | Bacon | 22591 | Animal House | 17173 | 22591 | 17173 |
| Kevin | Bacon | 22591 | Apollo 13 | 18979 | 22591 | 18979 |
| Tom | Hanks | 195428 | Apollo 13 | 18979 | 195428 | 18979 |
| Kevin | Bacon | 22591 | Few Good Men, A | 111813 | 22591 | 111813 |
| Kevin | Bacon | 22591 | Footloose | 116907 | 22591 | 116907 |
| Kevin | Bacon | 22591 | Hollow Man | 147603 | 22591 | 147603 |
| Kevin | Bacon | 22591 | JFK | 167324 | 22591 | 167324 |
| Kevin | Bacon | 22591 | Mystic River | 224842 | 22591 | 224842 |
| Kevin | Bacon | 22591 | Planes, Trains & Automobiles | 257264 | 22591 | 257264 |
| Kevin | Bacon | 22591 | Stir of Echoes | 314965 | 22591 | 314965 |
+------------+-----------+--------+------------------------------+--------+----------+----------+
10 rows in set (0.00 sec)
Code
Query so Far
select actors.first_name, actors.last_name, actors.id, movies.name, movies.id, roles.actor_id, roles.movie_id from actors join movies join roles on(roles.actor_id=actors.id and roles.movie_id=movies.id) where ((actors.id=roles.actor_id and first_name="Tom" and last_name="Hanks") or (first_name="Kevin" and last_name="Bacon" and movie_id=movies.id));
How can I filter out the actors who have appeared in similar movies with one query?
I want to filter out Tom Hanks and Kevin Bacon because they both appear in the movie Apollo 13.
I am given 3 tables, roles, movies, actors.
first_name, last_name, id are in actors
name, id are in movies
actor_id, movie_id are in roles
We are given the name of the actor Tom Hanks and Kevin Bacon.
This post was edited by bscit on Apr 8 2013 06:00pm