MySQL多表多对多关联查询：获取与指定演员同影片的其他演员

拥有多组关联数据表，需求是获取与指定演员（如John Doe）出演同一影片的其他演员。

我尝试了多种写法，最后尝试的SQL语句如下：

select video.id,video.title, actor.name,  @act_id := x.actor_id, y.actor_id
from actor
join video_actor x on actor.id = x.actor_id
join video_actor y on actor.id = y.actor_id and y.video_id = x.video_id
join video ON x.video_id = video.id
where actor.name = 'John Doe' or (y.actor_id <> @act_id)    

但始终无法得到正确结果。

参考优质答案后，得到了能正确获取目标数据的SQL语句：

select video.id, video.title, GROUP_CONCAT(resultActor.name), GROUP_CONCAT(y.actor_id)
from actor queriedActor
join video_actor x on queriedActor.id = x.actor_id
join video_actor y on y.video_id = x.video_id
join actor resultActor on resultActor.id = y.actor_id
join video ON x.video_id = video.id
where queriedActor.name = 'Stacy Cruz'
GROUP BY video.id, video.title;

此外，满足完整应用数据需求（包含影片制片方、分类、封面等信息）的SQL语句如下：

select video.id, video.title, GROUP_CONCAT(DISTINCT y.actor_id) as actors_id, GROUP_CONCAT(DISTINCT resultActor.name) as actors,producer.id,producer.name, GROUP_CONCAT(genre.name) as genres, MAX(pic.url) as cover
from actor queriedActor
join video_actor x on queriedActor.id = x.actor_id
join video_actor y on y.video_id = x.video_id
join actor resultActor on resultActor.id = y.actor_id
join video ON x.video_id = video.id
join producer ON video.producer_id = producer.id
join video_pic ON video.id = video_pic.video_id
join pic ON video_pic.pic_id = pic.id
join video_genre ON video.id = video_genre.video_id
join genre ON video_genre.genre_id = genre.id
where queriedActor.name = 'Stacy Cruz'
GROUP BY video.id, video.title,pic.cover
ORDER BY pic.cover DESC;

内容的提问来源于stack exchange，提问作者T' rip