Query within a query

Good evening all.



Firstly thankyou for taking the time to read this especially if you are able to offer any information, advice or solution.



Basically I am not the best at Access/SQL but as part of one of my assignments I have been given the task of creating a very simple database similar to IMDB.



The tasks I have to achieve are:

Quote:

Find all movies in which a particular actor has appeared. • Provide a list of all actors a particular actor has appeared with and a count of the number of times they have appeared together. • Find all films released between two dates and order by the box office grossings. • Find all films released in a particular year starring an actor with a particular nationality. This piece of coursework has the following three tasks: a. Create a diagram depicting the tables, columns and relationships for an RDBMS system to hold the data required to support the queries. Justify your normalisation approach, the design choices you’ve made and any assumptions you have made. b. Detail SQL queries to support the questions and the indexes required upon the data.

So far I have created the ERD Attachment 58045



I have used a junction table to link the many-many relationship between MOVIES and ACTORS. This has led me to create queries that:



- List all actors featured in a film

- All films featuring a particular actor



From the list of tasks I can extend my query to work out the date range and nationality etc not a problem.



What I can't do, because I am rubbish at this is figure out how to QUERY all films that feature two specific actors such as BRUCE WILLIS and WILL SMITH.



What I am trying to achieve is to run my query for all films by BRUCE WILLIS, and another query for all films by WILL SMITH and list any films which they both star in together.



All i have so far is this:

Quote:

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

Quote:

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.



If you have any ideas of how I can achieve the desired results, using SQL/Access it would be very much appreciated.



Thank you very much



Dave

Attached Images

relationship.jpg (89.1 KB)

Query within a query