JOIN 具有多对多关系的多个表
我有大约30张桌子。例如：与电影相关的制片人、导演等，
每个表由 movie_id 和 celeb_id 字段组成
，有一个表 celebs，其中包含以下字段： celeb_id celeb_name
对于特定 movie_id，我希望在单个查询中检索所有信息。
SELECT b_movies.*, bb_celebs.celeb_name
FROM b_movies
LEFT JOIN b_producer ON b_movies.id = b_producer.movie_id
LEFT JOIN bb_celebs ON b_producer.celeb_id = bb_celebs.celeb_id
LEFT JOIN b_director ON b_movies.id = b_director.movie_id
LEFT JOIN bb_celebs ON b_director.celeb_id = bb_celebs.celeb_id
WHERE b_movies.id = 'UNIQUE ID';
我想要与工作类型相关联的名人的名字：
Director
1. ABC
2. XYZ
Producer
1. ABC2
2. XYZ2
所有名人的名字都存储在 celebs 表中，其中包含 celeb_id 和 celeb_name 在制片人表 movie_id 和 celeb_id 中，相同的结构适用于导演等。所有其他表
请帮忙。
问候，
肯
I have around 30 tables. eg: producer, director etc related to movies
each table consists of movie_id and celeb_id field
there is a table celebs which consists of fields: celeb_id celeb_name
For a specific movie_id i want all the information retrieved in a single query.
SELECT b_movies.*, bb_celebs.celeb_name
FROM b_movies
LEFT JOIN b_producer ON b_movies.id = b_producer.movie_id
LEFT JOIN bb_celebs ON b_producer.celeb_id = bb_celebs.celeb_id
LEFT JOIN b_director ON b_movies.id = b_director.movie_id
LEFT JOIN bb_celebs ON b_director.celeb_id = bb_celebs.celeb_id
WHERE b_movies.id = 'UNIQUE ID';
I want the names of celebs associated with the type of work:
Director
1. ABC
2. XYZ
Producer
1. ABC2
2. XYZ2
All names of celebs are stored in celebs table with celeb_id and celeb_name
and in producer table movie_id and celeb_id and same structure applies to directors, etc.. all other tables
Please help.
Regards,
Kenh
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
...的适当值留作 OP 的练习...Appropriate values for
...are left as an excercise to the OP...(select 'Director', celeb.name from celebs c joindirector d on c.celeb_id = d.celeb_id and d.movie_id = "movie_id")
union all
(select 'Producer', celeb.name from celebs c join Producer p on c.celeb_id = p.celeb_id 和 p.movie_id = "movie_id")
联合所有
……
(select 'Director', celeb.name from celebs c join director d on c.celeb_id = d.celeb_id and d.movie_id = "movie_id")
union all
(select 'Producer', celeb.name from celebs c join producer p on c.celeb_id = p.celeb_id and p.movie_id = "movie_id")
union all
.....