how to query many-to-many?

Question

I found the following table structures while I was watching ruby on rails tutorial.

table actors id  int  11  primary key auto_increment name  varchar  30  table movies id  int  11  primary key auto_increment name  varchar  30  table actors_movies actor_id  int  11 movie_id  int  11 

How do I make a query to select movies that an actor is involved in?

I am not asking for ruby on rails code. I want the actual mysql query string.

Thank you!

Answer 1

Maybe something like this:

select m.name from movies m inner join actors_movies am on m.id = am.movie_id inner join actors a on am.actor_id = a.id where a.name = 'Christopher Walken' 

Answer 2

one thing to consider is that you are going to load the author object (because of RoR models), so with the ID would be enough:

select movies.id, movies.name from movies inner join actors_movies on actors_movies.movie_id=movies.id where actors_movies.actor_id=$actor_id