Unknown column 'R.movie_name' in 'on clause' for Inner join with subquery

Question

I tried to rename the table using join, but I'm running into the Unknown column 'R.movie_name' in 'on clause'. I referenced this question to correct, but I'm still get the same error.

What am I doing wrong?

My SQL query

  SELECT R.movie_name, AVG(S.similarity*R.rating)
 FROM Ratings AS R, Similarity S
 INNER JOIN (
    SELECT DISTINCT R.movie_name
    FROM Ratings R, Users U 
    WHERE U.name = 'Adam Brody' AND U.user_id != R.user_id
    ) AS MoviesAdamDidntWatch ON  R.movie_name = MoviesAdamDidntWatch.movie_name
 GROUP BY R.movie_name;

My Tables

CREATE TABLE Users (user_id INTEGER PRIMARY KEY,
                    name VARCHAR(100) NOT NULL);
CREATE TABLE Ratings (user_id INTEGER NOT NULL,
                      rating INTEGER NOT NULL,
                      movie_name varchar(100) NOT NULL,
                      PRIMARY KEY(user_id, movie_name),
                      FOREIGN KEY (user_id) REFERENCES Users(user_id));
CREATE TABLE Similarity (user1_id INTEGER NOT NULL,
                      user2_id INTEGER NOT NULL,
                      similarity FLOAT,
                      PRIMARY KEY (user1_id, user2_id),
                      FOREIGN KEY (user1_id) REFERENCES Users(user_id),
                      FOREIGN KEY (user2_id) REFERENCES Users(user_id));

Answer 1

You have used R twice as Alias in your query . You can fix it by replacing outer R with some other alias. like -

SELECT Rtg.movie_name, AVG(S.similarity*Rtg.rating)
 FROM Ratings AS Rtg, Similarity S
 INNER JOIN (
    SELECT DISTINCT R.movie_name
    FROM Ratings R, Users U 
    WHERE U.name = 'Adam Brody' AND U.user_id != R.user_id
    ) AS MoviesAdamDidntWatch ON  Rtg.movie_name = MoviesAdamDidntWatch.movie_name
 GROUP BY Rtg.movie_name;

But your query is not optimised, Consider optimising it by removing redundant inner query and cross products.I may help you with it if you post your table structure.