Bigquery - Column name name is ambiguous at [2:3]

Question

I am new to this - The below query is showing an error - employees and departments are tables and departmnet_id is the column name.

SELECT name, department_id, role FROM employee_data.employees INNER JOIN employee_data.departments ON 'august-emitter-173906.employee_data.employees.department_id' = 'august-emitter-173906.employee_data.departments.department_id'

Answer 1

This is because your department_id in your select could refer to either department id from departments or employees.

Try using an alias like:

SELECT 
emp.name, emp.department_id, emp.role 
FROM employee_data.employees emp
INNER JOIN employee_data.departments  dep
ON emp.department_id = dep.department_id

• Note: not sure which one is the correct table to use in your case but simply swap the alias as needed.

Example error: As you can see here, I am not using an alias, similar to your question, and there is an ambiguity error:

Example solution: With alias the ambiguity error is resolved