user7146708 user7146708 - 21 days ago 8
SQL Question

getting duplicates when joining tables

I have two tables that I want to join. Table1 has sales order, but it doesn’t have the name of the sales person. It only has employee ID. I have table2, that has the names of employees, and employeeID is common between the two tables. Normally I would use an inner join to get the name of the sales person from table2. The problem is that on table2, there are multiple entries for each employee. If they changed manager, or changed roles within the company, or perhaps went on FMLA, it creates a new row. Therefore, when I join the tables, it creates duplicates because of the multiple entries in table2. A sale shows 3 or 4 times in my results.

Select
a.state_name
,order_number
,a.employeeID
,b.Sales_Rep_Name
,a.order_date
from
table1 as A

Inner join table2 as B
On a.employeeid = b.employeeID

where
b.monthperiod = 'November' <-- If I remove this one it adds duplicates


Is there a way to not get these duplicates? I tried distinct but didn’t work. Probably because the rows have at least one column different. I was able to eliminate the duplicates when I added a where clause asking for last month on table 2, but I am in a situation where I need all months, not just one. I have to manually change the month in order to get the full year.

Any help would be appreciated. Thanks

LM3 LM3
Answer

Use a subquery to get list of distinct employee records and then query the sales table

Comments