Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

Find all movies with any actor in common

 I have an Access 2003 DB in support of a VB6 application.  The Access DB has two tables (named movies and ActorsLink) which are joined on the common field 'MovieID'.  The ActorsLink table contains every ActorID for all MovieID's in the DB.  (i.e. If 15 Actors appear in one movie, each of the 15 has a separate row along with the MovieID in question.)
  I'm looking for the correct syntax for a parameter query.  If a MovieID is entered, I need the query to return all other MovieID's and Titles which HAVE AT LEAST ONE ACTOR IN COMMON with the MovieID entered.
Does anyone know how to do this?
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This would return all movie titles where the MovieID = 1 and ActorID=2

SELECT Title FROM Movies INNER JOIN ActorsLink ON Movies.MovieID=ActorsLink.MovieID WHERE ActorsLink.MovieID=1 AND ActorsLink.ActorID=2

I'm making assumptions of your Table and Column names; you'll need to change them to match those in your project. In general, I've found it easiest to use the Query designer in Access to build your query, then you can just copy/paste the SQL into your VB project to build a recordset or whatever ...

The syntax for an Inner Join query is like this:

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

You can also use Right or Left joins (which return all records from the "right" or "left" table, with the Right generally being the first table specified).

See here for more syntax on Joins:
SELECT M.MovieidID, M.Movietitle
FROM Movies
inner join
(SELECT Actorlink.*
FROM Actorlink
WHERE Actorlink.Movieid=[enterid]) as q2
ON Actorlink.ActorID = Q2.ActorIdID) as q1
on Movies.Movieid = q1.Movieid
Yes, this needs the table ActorsLink twice: once for the movies-to-actors links, once for the actors-to-movies link... As a parameter query, it could be:

PARAMETERS [Enter Movie ID:] Long;
    Movies AS M
    INNER JOIN ActorsLink AS MA ON M.MovieID = MA.MovieID )
    INNER JOIN ActorsLink AS AM ON MA.ActorID = AM.ActorID
WHERE AM.MovieID=[Enter Movie ID:] AND M.MovieID<>[Enter Movie ID:];

PARAMETERS creates the parameter formally
SELECT DISTINCTROW is essential to show each movie only once
FROM uses ActorsLink twice (MA and AM)
WHERE selects the desired movies, but not itself.

Good luck!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now