Say you have three tables: Film, Role and Actor.
What's the best way to query the Film table to return the films featuring someone's favorite actors (starting from an array of actor IDs)? The film needs to feature all the actors from the list (not just one of them)
Right now, my best guess is to build the query dynamically like so:
var favoriteActorIds = [1,2,3];
var query = "SELECT * FROM Film ";
foreach(var id in favoriteActorIds)
query += "WHERE EXISTS ( // replacing the WHERE for AND after the first id
SELECT 1 FROM Role
WHERE Role.Id = Film.Id
AND Role.ActorId = " + id;
I'm sure I can get such an algorithm to work, but doing a new select or join for each actor just doesn't seem right.
