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.
intersect
can work fine but it's a very expensive operation if you have to do it for more than just a few queries, as it has to run each query individually, then process the result sets another time to find matches between the sets. \$\endgroup\$ – Phrancis Apr 19 '16 at 20:23intersect
can be the most efficient version as there's no aggregation &HAVING
. And using a list of values inWHERE
might forfeit index usage. \$\endgroup\$ – dnoeth Apr 19 '16 at 21:51