MySql Query Multiple tables, many to many, foreign keys. Need to get table rows associated with a se-CodePudding

I have multiple tables :

I need to get other actors in the same film as "John Doh"

I have tried many things, this is the last

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'

Using the excellent answer by Atmo I got the correct data using:

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;

and then in my app the full data requirement:

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;

CodePudding user response：

In your firsts 2 joins and where clause, you wrote: actor.id = x.actor_id = y.actor_id = <id of actor John Doe>.

What you want is for x.actor_id to have a chance to be different from y.actor_id. Like what you did for video_actor, you need to include your actor table more than once.

select video.id, video.title, resultActor.name, 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 = 'John Doe'

Optionally, if you want the actors who were in the same films as John Doe but not John Doe himself, do:

join video_actor y on y.video_id = x.video_id and y.actor_id <> x.actor_id

where queriedActor.name = 'John Doe' and resultActor.name <> 'John Doe'