SQL报错：Not unique table/alias: 'movie_direction' 求卡梅隆执导影片查询方案

Let's break down what's going wrong here and fix it step by step:

Why the error pops up #

You're joining the movie_direction table twice in your query without giving each instance a unique alias. Databases need a clear way to tell multiple references to the same table apart, which is why you're getting that "not unique table/alias" message. On top of that, your join conditions are misaligned—you tried linking movie.mov_id to movie_direction.dir_id, which doesn't make sense (those are movie IDs and director IDs, not matching keys).

Corrected SQL Query #

Here's the fixed version that properly connects the three tables and resolves the error:

SELECT mov_title 
FROM movie
INNER JOIN movie_direction ON movie.mov_id = movie_direction.mov_id
INNER JOIN director ON movie_direction.dir_id = director.dir_id
WHERE director.fname = 'james' AND director.lname = 'Cameron';

Key fixes explained: #

• Removed duplicate movie_direction join: We only need one join to this intermediate table, since its sole job is to connect the movie and director tables.
• Fixed join logic:
  • Link movie.mov_id to movie_direction.mov_id (matching movie IDs between the main movie table and the junction table)
  • Link movie_direction.dir_id to director.dir_id (matching director IDs between the junction table and the director table)
• No aliases required: Since each table is only referenced once, we don't need to assign aliases (though you could add them for readability, like movie_direction md if you wanted to shorten the name).

内容的提问来源于stack exchange，提问作者Saima