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!

Answers:

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.

Method 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'

Method 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

Method 3

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

Method 4

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x