Search code examples
sqlms-accessjoinaliasmysql-error-1066

Not unique table/alias: 'movie_direction'

schema I am trying to fetch the movies directed by james cameron.

select mov_title 
from movie 
inner join movie_direction on movie.mov_id = movie_direction.dir_id
inner join movie_direction on director 
where director.fname='james' and director.lname='Cameron';
Solution

  • You cannot refer to the movie_direction table twice in the same query without giving it an alias in at least one of the joins. Give alias in one of your join or in both.

    i.e. 

    inner join movie_direction md1 on {your condition}
inner join movie_direction md2 on {your condition}

    Your query should be like this

    SELECT 
 *
 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.dir_fname='james' and director.dir_lname='Cameron';