4
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ On your update, 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:23
  • \$\begingroup\$ Au contraire, depending on the actual data and indexing intersect can be the most efficient version as there's no aggregation & HAVING. And using a list of values in WHERE might forfeit index usage. \$\endgroup\$ – dnoeth Apr 19 '16 at 21:51
5
\$\begingroup\$

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.
\$\endgroup\$
  • 1
    \$\begingroup\$ ah! clever! you can do count(m.Id) = 2 here, too. it's probably important to note that there has to be a unique constraint on CastMember for this to work, though. If CastMember allowed duplicates this wouldn't work (but it does have that constraint, so this is great haha). \$\endgroup\$ – Joseph Nields Apr 19 '16 at 20:47
  • \$\begingroup\$ lol, now I'm embarrassed I haven't thought of that haha have an upvote =) \$\endgroup\$ – Mathieu Guindon Apr 19 '16 at 20:49
  • \$\begingroup\$ If CastMember allowed duplicates you could use COUNT(DISTINCT m.Actor) \$\endgroup\$ – Joseph Nields Apr 19 '16 at 21:06
  • 2
    \$\begingroup\$ If there were duplicates, I'd assume that means the actor played multiple roles in the same movie - in that case I'd probably want a Character table as well that joins (pun intended) in the fun. \$\endgroup\$ – Jesse C. Slicer Apr 19 '16 at 21:11
5
\$\begingroup\$

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:

yUML showing Movie, CastMember and Actor relationships and columns

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 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 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.

\$\endgroup\$
  • 1
    \$\begingroup\$ this still doesn't work. If we have Uma Thurman and John Travolta, we get Pulp Fiction (desired), Kill Bill (not desired--John's not in it), and Saturday Night Fever (not desired--Uma's not in it) because it's selecting movies where any of the actors match any of the ids in your in clause \$\endgroup\$ – Joseph Nields Apr 19 '16 at 20:03
  • \$\begingroup\$ maybe it's best to use an INTERSECT of three queries? \$\endgroup\$ – Joseph Nields Apr 19 '16 at 20:11
  • \$\begingroup\$ @JosephNields edited, ...not sure it's the best way though. \$\endgroup\$ – Mathieu Guindon Apr 19 '16 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.