Selecting all movies where specified actors worked together Here's a query I wrote trying to find all movies that three actors have been in together: 

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 INTERSECT

select Movie
from CastMember
where Actor = 1
intersect
select Movie
from CastMember
where Actor = 2
intersect
select Movie
from CastMember
where Actor = 3
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) &gt; 1 -- this should be replaced with the number of items in the "in" list minus 1.