How to fix beginner SQL INNER JOIN query error

September 5, 2023

I tried to replicate an exercise from an online course in my bigquery sandbox, but it gives me an error. Can someone explain to me what is the error meaning and possibly how to fix this query?

schema table employees: nome (string), id_departmento (integer), cargo (string)

schema table departments: nome (string), id_departmento (integer)

  SELECT
     employees.nome AS employees_name,
     employees.cargo AS employees_role,
     departments.nome AS departments_name
  FROM
    `primeiro-projeto-da.employee_data.employees`
  INNER JOIN
    employee_data.departments 
  ON
    employees.id_departmento = departments.id_departmento

It returns the following error –

Unrecognized name: employees at [10:3]

I checked the names and values but I don’t understand where is the mistake, as the syntaxe seems good to me.
I was expecting a table with the name of the employees, their role and the corresponding department.

Notice that there are small name differences due to translation of table names in the picture I provide, but as far as I can tell not significant or relevant to the exercise itself.

Thank you for your trouble.

>Solution :

This may be due to the absence of aliases for the "employees" and "departments" tables. You can try modifying your query by adding aliases for the tables as follows:

SELECT
  employees.nome AS employees_name,
  employees.cargo AS employees_role,
  departments.nome AS departments_name 
FROM
  `primeiro-projeto-da.employee_data.employees` AS employees
INNER JOIN
  employee_data.departments AS departments
ON
  employees.id_departmento = departments.id_departmento;

If it’s not because of aliasing, make sure the tables are linked by a foreign key.