Selecting all movies where specified actors worked together

Question

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

Answer 1

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.

Answer 2

distinct is pretty much always a sign that something's wrong.

I'm fighting with myself to try and see why this wouldn't produce exactly the same output:

select Movie from CastMember where Actor in (1,2,3) -- group by Movie

Seems your schema is confusing: it's hard to tell what's a FK and what's coming from where - that's probably why I'm over-simplifying the query here.

Let's see... I'm not a huge UML fan, so I might have gotten this wrong, but let's say you have this:

Then you'd want to use inner join to "link" the tables - having FK columns named with a [FKTableName][Id] scheme makes it much easier to see what's what IMO:

select m.Title
from Movie m
    inner join CastMember cm on m.Id = cm.MovieId
    inner join Actor a on a.Id = cm.ActorId
where a.Id in (1,2,3)
group by m.Title

---

Now, the above simply returns all movies any of actors 1, 2 and 3 have starred in - and you want the movies where all these actors have starred in together.

I'd work with the CastMember table at first, and select all rows with ActorId 1, 2, and 3:

select MovieId, ActorId from CastMember where ActorId in (1,2,3)

Then, I know I'm expecting 3 rows per movie - assuming SQL Server here, I could use a CTE and a windowing function to partition the results by MovieId and filter for movies that have 3 rows:

with filter as (
    select MovieId, row_number() over (partition by MovieId order by MovieId, ActorId) RN
    from CastMember
    where ActorId in (1,2,3))
select MovieId from filter where RN = 3

Now I know what Id's I'm after - If I make that select another CTE, I can now select all rows from Movie:

with filter as (
    select MovieId, row_number() over (partition by MovieId order by MovieId, ActorId) RN
    from CastMember
    where ActorId in (1,2,3)
)
, movies as (
    select MovieId from filter where RN = 3
)
select m.*
from Movie m
inner join movies on m.Id = movies.MovieId

And now I no longer have Kill Bill for Uma Thurman and John Travolta :)

---

Unfortunately mysql doesn't support CTE's and windowing functions (AFAIK). The equivalent would use much less elegant nested subqueries and temp tables; I'll leave it to a mysql reviewer to come up with a cleaner way - anyway I'd still recommend avoiding columns named after tables - a "Movie" column in a "Movie" table that means "Title", and then a "Movie" column in a many-to-many association table where "Movie" means "the primary key of the Movie table", definitely needs to be altered.