0

I have a DB that tracks movies and actors, with the following tables:

Person(ID, Name)
Movie(ID, Name)
Actors(ID, PersonID, MovieID)

ID fields are all primary keys and PersonID & MovieID are foreign keys.

I want to select all the movies that Tom Cruise and Brad Pitt played in. I tried to do the following:

SELECT * FROM Person, Actors
WHERE Person.ID = Actors.ActorID AND
(Person.Name= 'Tom Cruise' or Person.Name= 'Brad Pitt')
GROUP BY Actors.MovieID
HAVING COUNT(Actors.MovieID) > 1

This doesn't work, because the person name is not unique (and I can't change it to unique). If I have another actor named Brad Pitt and the two Brad Pitts played in the same movie it would return as a result too.

How can I do it?

NOTE: The number of actors I am querying about can change. I might need a movie with 10 actors that all played in it.

OHT
  • 15
  • 3

1 Answers1

1

Do a inner join between the tables as below

SELECT m.Name as MovieName 
FROM Movie m
inner join Actors a
on m.ID = a.MovieID
inner join Person p
on p.ID = a.ActorID 
and p.Name in ('Tom Cruise','Brad Pitt')
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • This does not force all the actors to play in the movie, only one. I need all of them. – OHT Jun 03 '14 at 12:01