djdannyp,
If this is course work and the table layout was given to you, you can probably stop reading here. Otherwise, continue.
While you have been offered a few solutions that should work with what you have, the real problem here is the database design. Relational database tables are not Excel spreadsheets and "Actor1, Actor2, Actor3, Actor4" is not the way to do it. That's not even
1NF.
Instead, consider the following three-table relationship:
tblFilms: ID, Title, Year, ...
tblActors: ID, FirstName, LastName, ...
tblActorsInFilms: FilmID, ActorID, Role, ...
The last table ties together actors and films in a many-to-many relationship. As the minimum, it would have the two ID columns to make the association.
Consider the advantages:
- Your films can now have more than four actors listed without
- Any query involving actors becomes much simpler and doesn't require dealing with four columns
- You can now write practical aggregate function queries, such as how many movies feature each actor
- You can now store additional details for an actor, such as date of birth and country. With that you could do spiffy things, such as find all films a particular actor starred in under the age of 25, films featuring Canadian actors and so forth.
- You can also add columns specific to the actor-film relationship to tblActorsInFilms. This could be the role they played, for example. That too could become a one-to-many relationship in a normalized database: sometimes an actor plays more than one role in a film. However, I won't be that thorough this time.
The query you asked about would then be as follows:
select F.ID, F.Title, F.Year
from tblFilms as F
join tblActorsInFilms as AF1 on AF1.FilmID = F.ID and AF1.ActorID = @ActorID1
join tblActorsInfilms as AF2 on AF2.FilmID = AF1.FilmID and AF2.ActorID = @ActorID2
Actors 1 and 2 were the parameters to the query, so I assume you don't need that returned in the results. If you do for some reason, that wouldn't be hard to add.
