Why is SQL query returning Parse error: ambiguous column name-CodePudding

I get

Parse error: ambiguous column name: ratings.rating

Why is that happening? I named all columns from which table they should come from...

SELECT 
    movies.title, ratings.rating 
FROM 
    movies, ratings 
JOIN 
    ratings ON movies.id = ratings.movie_id 
WHERE 
    movies.year = 2010 
ORDER BY 
    ratings.rating DESC, movies.title;

CodePudding user response：

The problem is this excerpt:

FROM movies, ratings
JOIN ratings

This includes two separate instances of the ratings table, which is allowed, but without any alias to distinguish between them, which causes an error.

We can fix this by removing the ,ratings:

FROM movies
JOIN ratings

FWIW, the A,B join syntax has been obsolete for 30 years now, and should no longer be used for new development. Especially don't mix it in the same query with the newer A JOIN B syntax, as it leads to mistakes like this.

Additionally, it is a good practice to use short mnemonic aliases for your tables, even if you don't think you'll need them:

SELECT m.title, r.rating 
FROM  movies m
JOIN ratings r ON m.id = r.movie_id 
WHERE  m.year = 2010 
ORDER BY r.rating DESC, m.title;