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 for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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'
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
Simple, just use the combined table to join the movie/actor tables:
Select m.name From actors a Inner Join actors_movies am On am.actor_id = a.id Inner Join movies m On m.id = am.movie_id Where a.name = @your_actor
select m.* from movies m inner join actors_movies am on am.movie_id = m.id inner join actors a on a.id = am.actor_id where a.someField = somevalue