Query within a query / Query based on results of another query

[lostlad]

Good evening all.



Firstly thank you for any help that you may be able to provide. (This is a continuation from https://www.accessforums.net/databas...tbl-51469.html)

I have a task to create a Movie database similar in functionality to IMDB whereby I can search for stars featured in a particular movie, also a search for all movies featuring a particular star.



As you can see by the relationship diagram it is simple. Movies and actors are linked by a junction/link box to break up the many-many connection.

I am trying to create a query that will identify all films that feature two particular actors. For example, what films have featured both Will Smith and Bruce Willis (fictional data).

I assume i need to run two queries to gather the data and then somehow perform a third query to match the films. Here is my query data for the actors to see what movies they star in. If anybody has any advice or preferably a solution it would be greatly appreciated.

Here are my queries:

SELECT
Actors_TBL.actor_ID AS Actors_TBL_actor_ID, Actors_TBL.actor_gender, Actors_TBL.actor_First_Name, Actors_TBL.actor_Last_Name, Actors_TBL.Nationality_LKP, Movies_TBL.movie_ID AS Movies_TBL_movie_ID, Movies_TBL.movie_title, Movies_TBL.movie_description, Movies_TBL.movie_genre, Movies_TBL.date_release, Movies_Actors_LINK.movie_ID AS Movies_Actors_LINK_movie_ID, Movies_Actors_LINK.actor_ID AS Movies_Actors_LINK_actor_ID


FROM Movies_TBL INNER JOIN (Actors_TBL INNER JOIN Movies_Actors_LINK ON Actors_TBL.[actor_ID] = Movies_Actors_LINK.[actor_ID]) ON Movies_TBL.[movie_ID] = Movies_Actors_LINK.[movie_ID]
WHERE (((Actors_TBL.actor_First_Name)="will") AND ((Actors_TBL.actor_Last_Name)="smith"));

and

SELECT
Actors_TBL.actor_ID AS Actors_TBL_actor_ID, Actors_TBL.actor_gender, Actors_TBL.actor_First_Name, Actors_TBL.actor_Last_Name, Actors_TBL.Nationality_LKP, Movies_TBL.movie_ID AS Movies_TBL_movie_ID, Movies_TBL.movie_title, Movies_TBL.movie_description, Movies_TBL.movie_genre, Movies_TBL.date_release, Movies_Actors_LINK.movie_ID AS Movies_Actors_LINK_movie_ID, Movies_Actors_LINK.actor_ID AS Movies_Actors_LINK_actor_ID

FROM Movies_TBL INNER JOIN (Actors_TBL INNER JOIN Movies_Actors_LINK ON Actors_TBL.[actor_ID] = Movies_Actors_LINK.[actor_ID]) ON Movies_TBL.[movie_ID] = Movies_Actors_LINK.[movie_ID]
WHERE (((Actors_TBL.actor_First_Name)="bruce") AND ((Actors_TBL.actor_Last_Name)="willis"));

I am trying to achieve a query that given two names (query 1 and 2) it will list all films which star both actors.

Thank you very much

[June7]

What if you wanted 3 or 4 or more actors?
Multiple criteria on same field best done with IN operator. Review http://allenbrowne.com/ser-50.html

[lostlad]

Thankyou very much June7.

My requirement criteria is only to find a movie featuring a person and a specific other however you are right. This question could be extended to match a larger cast, 3,4 actors etc.

I will read your recommended link now and let you know how i get on.

Thank you very much as always