BigQuery error message help, I'm a beginner. Table name missing dataset?? What can I try to remedy this? [duplicate]

Question

Error running query Table name "departments" missing dataset while no default dataset is set in the request.

My table is named "departments" EXACTLY as it is seen here. I've checked it and had a couple others look at my spelling to make sure it's correct. Our eyes work and it is in fact correct. So what is the glitch? Has anyone else run into this? I'm working through Big Query for the Google Data Analytics course.

I've tried my query with many variations, using back-ticks, quotations, parenthesis, I've rearranged the order, rewritten it a few ways and I get nothing.

I uploaded my data set exactly as they said to, location US, automatic schema, correct table name. Not sure what else to do.

Here are a couple screen shots for some reference. enter image description here

Answer 1

I'm doing the same course and I ran into this exact error. One thing to note is that you can't mix and match table reference styles, meaning if you included the full path that BigQuery tends to auto-fill in back-ticks in at least one spot, then you need to use that style everywhere. This includes all the column names in the SELECT clause and the table names in the FROM and JOIN clauses. If you've tried the back-ticks in parts of your code but not everywhere, that might explain why it wasn't running.

SELECT 
    `esoteric-cider-333608.employee_data.employees`.name AS employee_name,
    `esoteric-cider-333608.employee_data.employees`.role AS employee_role,
    `esoteric-cider-333608.employee_data.departments`.name AS department_name
FROM 
    `esoteric-cider-333608.employee_data.employees`
INNER JOIN 
    `esoteric-cider-333608.employee_data.departments`
ON 
    `esoteric-cider-333608.employee_data.employees`.department_id = `esoteric-cider-333608.employee_data.departments`.department_id; 

A better way of doing this is to alias the table names. You can do this the same way you alias column names in the SELECT clause by using the AS keyword. Now you can refer to the table in shorthand throughout your statement and BigQuery will have an exact definition for the "departments" table.

SELECT 
    employees.name AS employee_name,
    employees.role AS employee_role,
    departments.name AS department_name
FROM 
    `esoteric-cider-333608.employee_data.employees` AS employees
INNER JOIN 
    `esoteric-cider-333608.employee_data.departments` AS departments
ON 
    employees.department_id = departments.department_id; 

For more information about aliases visit https://www.w3schools.com/sql/sql_alias.asp.