CODEWITHSUNDEEP
google-bigquery
David Salomon
David Salomon

Reputation: 849

Duplicates with LEFT JOIN on historical roster

I have a table where I have employee data by week... Let's say I just have 3 employees. I need to track their information weekly, because we might change their supervisor in any week and employee performance will also count towards supervisor performance

week employeeId employeeName supervisor
2022-07-10 1 David Bob
2022-07-10 2 Joe Bob
2022-07-10 3 Miriam Martin
2022-07-17 1 David Bob
2022-07-17 2 Joe Bob
2022-07-17 3 Miriam Martin

I have another table, where I track sales, I just have the employeeId showing to that table.

week employeeId sales
2022-07-10 1 $500
2022-07-10 2 $400
2022-07-10 3 $309

I want to create a new table that show how's their supervisor with left join

FROM company.sales AS t    
LEFT JOIN company.employee_roster AS roster
  ON
    roster.week = t.week
    AND roster.employeeId = t.employeeId

But I always get duplicated data... If I remove the JOIN statement, I get the data as intended without the supervisor's name.

Is the above approach the right one? Is there any better way to join those tables?

Upvotes: 0

Views: 126

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173200

Use below

SELECT *
FROM `company.sales` AS t    
LEFT JOIN `company.employee_roster` AS roster
USING(week, employeeId)

with output

enter image description here

or just use below (this cover scenario when columns to be joined can have different names)

SELECT t.*, employeeName, supervisor
FROM `company.sales` AS t    
LEFT JOIN `company.employee_roster` AS roster
ON t.week = roster.week
AND t.employeeId = roster.employeeId

Upvotes: 2

Related Questions