how to query many-to-many?

mysql many-to-many
Moon picture Moon · May 7, 2009 · Viewed 38.3k times · Source

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

Andy White picture Andy White · May 7, 2009

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'

Related questions

SQL JOIN many-to-many

Sorry about the minimalistic title but I don't know how to describe it in short. I have three tables: The table of groups ID | Genre ----------------- 1 | Action 2 | Adventure 3 | Drama Many to many table GroupID | ElementID ----------------- 3 | 1 1 | 2 2 | 2 2 | 3 3 | 3 And the table of …

Read More
getting the value of an extra pivot table column laravel

I have a phone_models, phone_problems, and a phone_model_phone_problem pivot table. The pivot table has an extra column 'price'. PhoneModel: class PhoneModel extends \Eloquent { public function problems() { return $this->belongsToMany('RL\Phones\Entities\PhoneProblem')-&…

Read More
SQL many to many select

category_product --------------- id_category id_product product --------------- id_product id_manufacturer manufacturer --------------- id_manufacturer name How would I create an SQL query so that it selects all the names from manufacturer when id_category is equal to …

Read More