April 23, 2013 at 12:10 pm
I have gathered employee data
SELECT
EmpID,
LName,
FName,
StationID
FROM emp.employees
Provides me with my employee list of 2475
This works fine until I do
SELECT
a.EmpID,
a.LName,
a.FName,
a.StationID,
b.Region
FROM emp.employees a
JOIN emp.division b
ON a.StationID=b.StationID
I am getting multiple records back for the same person. I even tried a left join.
Thoughts on how to make sure I can add the Region and only get the 2475 records back?
April 23, 2013 at 12:31 pm
Sounds like the first query did not return dupes (multiple records for the same person) so there must be multiple occurrences of the same station id from emp.division. You could check this by:
SELECT * FROM
(
SELECT StationID, COUNT(*) C
FROM emp.division
GROUP BY StationID
) COUNTS
WHERE C > 1
April 23, 2013 at 12:36 pm
Correct the first query did not produce duplicates. The second does.
April 23, 2013 at 12:46 pm
This will probably get you what you want.
select EmpID,
LName,
FName,
StationID,
Region
from
(
SELECT
a.EmpID,
a.LName,
a.FName,
a.StationID,
b.Region,
ROW_NUMBER() over(partition by a.EmpID order by a.EmpID) as RowNum
FROM emp.employees a
JOIN emp.division b
ON a.StationID=b.StationID
) x
where x.RowNum = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 12:52 pm
what you could do is see if the employee table ALSO has some other column you could use to further select down which row from table division you need to actually use. then if so, join the two tables on both stationid and division. Just posting in case that's the real situation, or there could alternatively be some sort of business rule that determines which row in the division table you need to link to that employee. Seems sort of unusual that you'd get multiple divisions per employee, and in which case, Seans solution could be returning a region that might not apply especially if stationids are reused in different regions, when there may be an actual correct way to retrieve the employee's region.
April 23, 2013 at 1:12 pm
patrickmcginnis59 10839 (4/23/2013)
what you could do is see if the employee table ALSO has some other column you could use to further select down which row from table division you need to actually use. then if so, join the two tables on both stationid and division. Just posting in case that's the real situation, or there could alternatively be some sort of business rule that determines which row in the division table you need to link to that employee. Seems sort of unusual that you'd get multiple divisions per employee, and in which case, Seans solution could be returning a region that might not apply especially if stationids are reused in different regions, when there may be an actual correct way to retrieve the employee's region.
Very true. Of course if we had ddl and sample data there would be no speculation. 😀
OP - if you could take a few minutes and read the first article in my signature for best practices when posting questions it would help us greatly to help you solve your issue. Without these kinds of details the best we can do is take a shot in the dark and see if maybe it helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply