Correct syntax for table name under Inner Join?

Question

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

score 0 · Accepted Answer · answered Jan 23 '22 at 19:47

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)

Correct syntax for table name under Inner Join?

1 Answers1