Search code examples
mysqlmany-to-many

how to query many-to-many?

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!

Solution

  • 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