patternsqlMinor
Selecting all movies where specified actors worked together
Viewed 0 times
actorsmoviesallworkedwheretogetherselectingspecified
Problem
Here's a query I wrote trying to find all movies that three actors have been in together:
Movie and Actor are FKs to a Movie and Actor table. CastMember just has two columns: Movie and Actor.
Is there a better way to write this?
Update: thinking of just using
select distinct
Movie
from
CastMember
where
Movie in (
select Movie
from CastMember
where Actor = 1
)
and Movie in (
select Movie
from CastMember
where Actor = 2
)
and Movie in (
select Movie
from CastMember
where Actor = 3
)Movie and Actor are FKs to a Movie and Actor table. CastMember just has two columns: Movie and Actor.
Is there a better way to write this?
Update: thinking of just using
INTERSECTselect Movie
from CastMember
where Actor = 1
intersect
select Movie
from CastMember
where Actor = 2
intersect
select Movie
from CastMember
where Actor = 3Solution
Will this do the trick?
select m.Title
from Movie m
inner join CastMember cm on cm.MovieId = m.Id
inner join Actor a on cm.ActorId = a.Id
where a.Name in ('John Travolta', 'Uma Thurman')
group by m.Title
having count(m.Id) > 1 -- this should be replaced with the number of items in the "in" list minus 1.Code Snippets
select m.Title
from Movie m
inner join CastMember cm on cm.MovieId = m.Id
inner join Actor a on cm.ActorId = a.Id
where a.Name in ('John Travolta', 'Uma Thurman')
group by m.Title
having count(m.Id) > 1 -- this should be replaced with the number of items in the "in" list minus 1.Context
StackExchange Code Review Q#126157, answer score: 6
Revisions (0)
No revisions yet.