Correct syntax for table name under Inner Join?

January 23, 2022

I am a complete beginner to BigQuery, and I am trying to create an inner join between two table names, where the column ‘title’ is the joining column. I believe my syntax is correct, but I do not know what I am doing wrong when I input the ON clause. Here is my syntax:
SELECT
*
FROM
book-to-film-adaptations.movies.movies_metadata_relevant
JOIN
book-to-film-adaptations.goodreads_books.goodreads_books_relevant_data
ON
movies_metadata_relevant.title = goodreads_books_relevant_data.title

I get this error message: Unrecognized name: movies_metadata_relevant at [8:3]
I have tried it with the full names (book-to-film-adaptations.movies.movies_metadata_relevant), but then I get an error message: "Syntax error: Unexpected keyword TO"

Any suggestions?
Thanks

>Solution :

You need to alias tables and use those like in below example – but in this case you will need

... 
  ...
FROM
  `book-to-film-adaptations.movies.movies_metadata_relevant` t1
JOIN
  `book-to-film-adaptations.goodreads_books.goodreads_books_relevant_data` t2
ON
  t1.title = t2.title

or if join columns have same name (like in your case) you can use below version

... 
  ...
FROM
  `book-to-film-adaptations.movies.movies_metadata_relevant` t1
JOIN
  `book-to-film-adaptations.goodreads_books.goodreads_books_relevant_data` t2
USING (title)