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!

You may want to put your code sample in a code block - just put four spaces before each line of it, and put a blank line before and after the block. — bdonlan, May 07 '09 at 02:07

score 53 · Answer 1 · answered May 07 '09 at 02:05

53

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'

answered May 07 '09 at 02:05

Andy White

86,444
48
176
211

Is there any way to get only movies with combination of actors like IN ('Christopher Walken', 'John Doe') @AndyWhite – mcek Jan 16 '19 at 12:57
1

@mcek you can do it using subqueries like this: [pasted code](https://pastebin.com/5P9bw9dD). Another way is a "Self Join": [pasted code](https://pastebin.com/mTCJAH40). But in both cases query will grow with the quantity of actors in combination. So they are not universal but can be auto generated. – chill appreciator Aug 29 '19 at 15:05

score 21 · Accepted Answer · answered May 07 '09 at 02:07

21

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

answered May 07 '09 at 02:07

fesja

3,313
6
30
42

it is incomplete, the best is the next answer – Christiano Matos May 10 '18 at 14:42
2

@Christiano Given the nature of StackExchange, the “next answer” is undefined. Whose answer do you mean? – Frungi Oct 12 '19 at 19:15
@Frungi Probably the most voted one ~ By Andy White. – Pathros Feb 14 '20 at 15:54

Justin Ethier · Answer 3 · 2009-05-07T02:11:02.383

10

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

edited May 07 '09 at 02:11

answered May 07 '09 at 02:06

Justin Ethier

131,333
52
229
284

Is there any way to get only movies with combination of actors like IN ('Christopher Walken', 'John Doe') @JustinEthier – mcek Jan 17 '19 at 06:44
@mcek - Sure, just use an `OR` in the `WHERE` clause. – Justin Ethier Jan 17 '19 at 16:40

score 2 · Answer 4 · answered May 07 '09 at 02:08

2

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

answered May 07 '09 at 02:08

razenha

7,660
6
37
53

how to query many-to-many?

4 Answers4

Linked