HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Selecting all movies where specified actors worked together

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
actorsmoviesallworkedwheretogetherselectingspecified

Problem

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

Solution

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.