Problem with INNER JOIN

[Spica]

I have a movie database with a searchfunction that have trouble making a query for.

My SQL that doesn't work is this:

SELECT DISTINCT TapeNr, Title, StartTime, EndTime
FROM Movies
INNER JOIN Actors
ON Movies.ID = Actors.MovieID
WHERE Actors.Actor = "Bruce Willis" AND Actors.Role = "Hans Gruber"

The problem with this SQL is that a movie is only selected if it has an actor named "Bruce Willis" with the role "Hans Gruber".

My intention is that if a movie has an actor named "Bruce Willis" and an other actor (or the same actor) has a role "Hans Gruber" the movie should be selected.

Does anyone have a solution?

 

[Mod]

WHERE Actors.Actor = "Bruce Willis" OR Actors.Role = "Hans Gruber"

?

 

[Spica]

It isn't that simple...

No, both the actor "Bruce Willis" and the role "Hans Gruber" must be in the movie.

 

[Mod]

lol, i thought that looked too easy. This is seems similiar to a problem i was having earlier today.

The solution ken gave me was really good. Go to the query builder window, and have it show three tables: your movie table once, and your actors table twice.

Link the tblMovie.actor field to the tblActor.Name field and the tblActor_1.role field.

Then bring tblMovie.Name, tblActor.Name, and tblActor_1.role fields down into the select grid, and try running the query it makes.


The key is to make access know to treat the actor table as two tables.

 

[Spica]

Thanks!

It worked perfectly!

I owe you one!