Moon Moon - 1 month ago 6
MySQL Question

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!

Answer

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
Comments