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

Im not sure so I'm posting this as a comment. I think you should remove the `"AS"` in `FROM Ratings AS R, Similarity S` — Naxos84, Sep 27 '17 at 05:44
`R` specified as an alias twice in query, one more issue how do you relate Similarity table with query, right now its doing cross product — M Khalid Junaid, Sep 27 '17 at 05:46

score 1 · Accepted Answer · answered Sep 27 '17 at 05:58

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.

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

1 Answers1