Ms-access – Combining two queries

ms access

I am trying to write code for this query:

Find the names of the actors that played at least in one movie since 2000 and where not directors of any movie at this period of time.

My thought was to split this query into two, then to merge the two parts. The first part will be code that finds the names of the actors that played in at least one movie since 2000. The second code will be a query that finds the actors who were not directors of any movie at this period of time.

I have this two queries:

Code for part one:

SELECT DISTINCT Actors.FirstName, Actors.LastName
FROM (Actors INNER JOIN ActorsMovies ON Actors.ActorID=   ActorsMovies.ActorID) 
INNER JOIN Movies ON ActorsMovies.MovieID=Movies.MovieID
GROUP BY ActorsMovies.MovieID, Actors.FirstName, Actors.LastName, Movies.Year
HAVING COUNT(ActorsMovies.MovieID)>0 AND Movies.Year>=2000;

Code for part two:

WHERE SELECT DISTINCT Directors.FirstName, Directors.LastName
FROM( (Directors 
INNER JOIN DirectorsMovies ON    Directors.DirectorID=DirectorsMovies.DirectorID)
INNER JOIN Movies ON  DirectorsMovies.MovieID=Movies.MovieID)
GROUP BY Movies.Year, Directors.FirstName, Directors.LastName
HAVING Movies.Year>=2000);

These two queries work.My difficulty is in merging them. I tried using NOT EXISTS here is my attempt:

 SELECT DISTINCT Actors.FirstName, Actors.LastName
 FROM (Actors INNER JOIN ActorsMovies ON Actors.ActorID= ActorsMovies.ActorID) INNER JOIN Movies ON ActorsMovies.MovieID=Movies.MovieID
 GROUP BY ActorsMovies.MovieID, Actors.FirstName, Actors.LastName, Movies.Year
 HAVING COUNT(ActorsMovies.MovieID)>0 AND Movies.Year>=2000
 WHERE NOT EXISTS(
 SELECT DISTINCT Directors.FirstName, Directors.LastName
 FROM( (Directors INNER JOIN DirectorsMovies ON   Directors.DirectorID=DirectorsMovies.DirectorID)
 INNER JOIN Movies ON  DirectorsMovies.MovieID=Movies.MovieID)
 GROUP BY Movies.Year, Directors.FirstName, Directors.LastName
 HAVING Movies.Year>=2000);

Best Answer

There are some problems with your attempt

  1. WHERE is supposed to be before the GROUP BY (I assume this is true for ms-access)
  2. The EXISTS predicate will be TRUE if there is a director, regardless if this has anything to do with the actor under investigation.

For the first part I would try something like:

SELECT Actors.FirstName, Actors.LastName
FROM (Actors 
      INNER JOIN ActorsMovies 
          ON Actors.ActorID = ActorsMovies.ActorID
     ) 
INNER JOIN Movies 
    ON ActorsMovies.MovieID=Movies.MovieID
WHERE Movies.Year>=2000    
GROUP BY Actors.FirstName, Actors.LastName

The parenthesis makes it a bit difficult to indent the query, but if I got it right ms-access requires them when more than two tables are joined.

Instead of GROUP BY we can use distinct as in:

SELECT DISTINCT Actors.FirstName, Actors.LastName
FROM (Actors 
      INNER JOIN ActorsMovies 
          ON Actors.ActorID = ActorsMovies.ActorID
     ) 
INNER JOIN Movies 
    ON ActorsMovies.MovieID=Movies.MovieID
WHERE Movies.Year>=2000

Now, for every actor that satisfies these predicates we would like to make sure that they don't directed any movies after 2000

SELECT DISTINCT Actors.FirstName, Actors.LastName
FROM (Actors 
      INNER JOIN ActorsMovies 
          ON Actors.ActorID = ActorsMovies.ActorID
     ) 
INNER JOIN Movies 
    ON ActorsMovies.MovieID=Movies.MovieID
WHERE Movies.Year>=2000    

  AND NOT EXISTS ( 
      SELECT 1 -- anything will do, it's the existence of the row that's
               -- important
      FROM (Directors 
            INNER JOIN DirectorsMovies 
                ON   Directors.DirectorID=DirectorsMovies.DirectorID
           )
      INNER JOIN Movies 
          ON  DirectorsMovies.MovieID=Movies.MovieID
      WHERE Movies.Year>=2000
        -- Correlate this sub-query with the first query
        AND Directors.FirstName = Actors.FirstName
        AND Directors.LastName = Actors.LastName
  )

I assumed here that both an Actor and a Director can be identified by their first- and last- name. This is probably not true in real life, but it is difficult to guess how they relate to each other without descriptions of the tables.

This probably wont work out of the box, but hopefully you get some ideas that will help you.

Related Question