sql & inner join-CodePudding

I'm having a challenge with INNER JOIN in BigQuery. When trying to run this, I get following error message:

Unrecognized name: employees at [8:2]’ with line 8 being employees.department_id =departments.department_id

SELECT 
 name, role, department_id
FROM
`dataanalysis001.employee_data.employees`
 INNER JOIN
 `dataanalysis001.employee_data.departments` 
 ON
 employees.department_id =departments.department_id

Any suggestions would be welcome. Thanks

CodePudding user response：

You need to either use the fully qualified name for a table every time you use it or, preferably, alias it and use the alias.

so either:

SELECT 
 name, role, department_id
FROM
`dataanalysis001.employee_data.employees`
 INNER JOIN
 `dataanalysis001.employee_data.departments` 
 ON dataanalysis001.employee_data.employees.department_id 
= dataanalysis001.employee_data.departments.department_id

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

CodePudding user response：

I think you need to alias your tables.

In Postgres case.

 SELECT name, role, department_id

       FROM`dataanalysis001.employee_data.employees` employees    

         INNER JOIN `dataanalysis001.employee_data.departments` departments

            ON employees.department_id = departments.department_id

In MySQL

 SELECT name, role, department_id

       FROM`dataanalysis001.employee_data.employees` AS employees    

         INNER JOIN `dataanalysis001.employee_data.departments` AS departments

            ON employees.department_id = departments.department_id

CodePudding user response：

i need more information. By on you usually compare a foreign key with a primary key

 ON
 employees.department_id =departments.id

but if its the databse structure then is it ok. Her is an example of an inner join

SELECT Authors.AuthorID, Books.name, Books.date

FROM Authors INNER JOIN Books ON Authors.BookId=Books.id;

try use by select the table name in front

  SELECT 
d.name, e.role, e.department_id
FROM
`dataanalysis001.employee_data.employees` as e
 INNER JOIN
 `dataanalysis001.employee_data.departments` as d
 ON
 e.department_id = d.department_id;

i hope i can help