Why is SQL query returning Parse error: ambiguous column name

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;

Schema

CREATE TABLE movies 
(
    id INTEGER,
    title TEXT NOT NULL,
    year NUMERIC,
    PRIMARY KEY(id)
);

CREATE TABLE stars 
(
    movie_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    FOREIGN KEY(movie_id) REFERENCES movies(id),
    FOREIGN KEY(person_id) REFERENCES people(id)
);

CREATE TABLE directors 
(
    movie_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    FOREIGN KEY(movie_id) REFERENCES movies(id),
    FOREIGN KEY(person_id) REFERENCES people(id)
);

CREATE TABLE ratings  
(
    movie_id INTEGER NOT NULL,
    rating REAL NOT NULL,
    votes INTEGER NOT NULL,
    FOREIGN KEY(movie_id) REFERENCES movies(id)
);

CREATE TABLE people 
(
    id INTEGER,
    name TEXT NOT NULL,
    birth NUMERIC,
    PRIMARY KEY(id)
);

>Solution :

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;