Not unique table/alias: 'movie_direction'

Question

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';

Answer 1

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';

Answer 2

This should work:

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

Error in your query was you were joining mov_id with dir_id and only one join is required for this.