sqlite3: near "." : syntax error

Question

I have two tables already created:

create table movies(id integer, name text, score integer);
create table cast(movie_id integer, cast_id integer, cast_name text);

I need the first 10 (distinct, alphabetically by cast_name) cast members and their average movie scores, so I tried:

select movie_id,cast_id,cast_name,id,score from cast,movies 
where movies.id=cast.movie_id and cast_name in 
(select distinct cast_name from cast order by cast_name limit 10);

But then I got an error message: near "." : syntax error

After that, I tried to make it simpler:

select cast_name, score from cast,movies where movies.id=cast.movie_id;

I still got the same error.

I guess this might be because '.' is a special command in sqlite3, but cannot figure out how to solve this problem.

Any help will be appreciated.

Answer 1

cast is a reserved word. The list of reserved words is here.

select cast_name, score
from `cast` c join
     movies m
     on m.id = c.movie_id;

You can escape it using backticks or double quotes. This query uses table aliases to simplify the query and more modern syntax for the join.