我有多个表：

我需要找其他演员和"杜约翰"演同一部电影
我试过很多方法，这是最后一次

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)

I am unable to figure out how to put together a query that will give me the other actors when I specify where actor.name = 'John Doe'
使用优秀的答案由Atmo我得到了正确的数据使用：

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;

然后在我的应用程序中，完整的数据要求：

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;