I found the following table structures while I was watching ruby on rails tutorial.
我在观看ruby on rails教程时发现了以下表格结构。
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.
我不是在rails代码上要求ruby。我想要实际的mysql查询字符串。
Thank you!
谢谢!
4 个解决方案
#1
14
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:
要考虑的一件事是你要加载作者对象(因为RoR模型),所以使用ID就足够了:
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
#2
28
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'
#3
5
Simple, just use the combined table to join the movie/actor tables:
很简单,只需使用组合表来连接movie / actor表:
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
#4
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
#1
14
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:
要考虑的一件事是你要加载作者对象(因为RoR模型),所以使用ID就足够了:
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
#2
28
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'
#3
5
Simple, just use the combined table to join the movie/actor tables:
很简单,只需使用组合表来连接movie / actor表:
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
#4
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