Cartesian product as a result of inner join

Question

I have two tables:actors and movies, and a third table role, which connects them. ActorId is the primary key for actors. MovieCode is the primary key for the movies table. I have a page of an actor where I need to show the date when the movie was published, the name of the movie and the role of the actor. My query is:

SELECT mo.date, mo.title, role.roleDescription
FROM movies mo, role
inner join actors
on role.actorId=actors.actorId
inner join movies 
on role.movieCode=movies.movieCode
where role.actorid=2

The query returns me the Cartesian product. What could be the problem?Thank you in advance!

Answer 1

Try this one ,since you want to select the movies and roles of actor for a specific actor you can skip the join on actor table, also in your query you are joining movies two times

SELECT mo.date, mo.title, r.roleDescription
FROM movies mo, 
INNER JOIN role r
ON r.movieCode=mo.movieCode
WHERE r.actorid=2

Answer 2

The problem is that you're joining to movies twice: once as mo, and once as movies. mo is never mentioned in any ON clause or WHERE clause, so you never filter out any of the values of mo. To fix it, just remove the movies mo, part, and change the fields in your SELECT-list to use movies instead of mo:

SELECT movies.date, movies.title, role.roleDescription
FROM role
inner join actors
on role.actorId=actors.actorId
inner join movies 
on role.movieCode=movies.movieCode
where role.actorid=2

Also, you don't really need the join to actors, since you're not selecting anything from actors and you already know what value of actorid you want. So removing that, and restructuring a bit for clarity:

SELECT movies.date,
       movies.title,
       role.roleDescription
  FROM movies
  JOIN role
    ON role.movieCode = movies.movieCode
 WHERE role.actorid = 2
;