I'm struggling a bit with this query. Perhaps someone can give me a hint?
My setup consists of 3 tables (Actors, Movies, and Relations). What I want to do is:
Find all actors whose name starts with an A that play in any movie alongside Person1.
FROM Actors a, Movies m, Relations r
AND Person1.keyPerson = m.KeyPerson
AND m.KeyMovies ....
There are several ways you can do this - one method you can do is via an
IN statement, pulling the movies
Person1 has been in, and checking the
A% person's movies.
Select Distinct A.Name From Actors A Join Relations R On R.ActorId = A.ActorId Where A.Name Like 'A%' And R.MovieId In ( Select R2.MovieId From Actors A2 Join Relations R2 On R2.ActorId = A2.ActorId Where A2.Name = 'Person1' )
Another method is via an
EXISTS statement, looking for any movies that
Person1 has been in that
A% has also been in:
Select A.Name From Actors A Join Relations R On R.ActorId = A.ActorId Where A.Name Like 'A%' And Exists ( Select * From Movies M2 Join Relations R2 On R2.MovieId = M2.MovieId Join Actors A2 On R2.ActorId = R2.ActorId Where A2.Name = 'Person1' And M2.MovieId = R.MovieId )