• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Duplicates from my join

Ive got the following and its giving me duplicate results

The employjobs ej table has many occurunences of the UID becase its stores any changes in the job titles or salary for the UID
It stores the changes against ej.sequence number which always decreases when there is an update to ej.salary or ej.jobtitle


So I ammended to the below to try and  include ej.sequence MIN so that it would try and pick only the record that matched with the last relevant number

But still getting duplicates ?

Any ideas please

select DISTINCT
 
e.EmployeeID,
es.EmployeeStatus AS Status,
e.EmployeeDescNoID AS Emp_Name,
e.WindowsUserName AS LoginID,
e.DisplayEmployeeID AS UserID,
e.ExtNoUD AS Extension,
e.FFWMobileUD AS FFWMobile,
e.WorkEmail AS Email,
-- profitcentre
-- department
e.EliteNoUD AS SectionNumber,
e.SecretaryNameUD AS Secretary,
e.LocationUD AS Location,
ej.PostID AS Position
 
FROM [Cascade].dbo.Employee e
 
JOIN [Cascade].dbo.EmployeeJobs ej
  ON e.EmployeeID = ej.EmployeeID
 
JOIN [Cascade].dbo.Employee_Status es
  ON e.EmployeeID = es.EmployeeID
 
LEFT OUTER JOIN (select t1.JobTitle, t1.EmployeeID
                   from [Cascade].dbo.EmployeeJobs t1 
                   join (select employeeId, min(Sequence) as sequence 
                           from [Cascade].dbo.EmployeeJobs 
                          group by employeeID
                        ) t2
                     on t1.employeeid = t2.employeeID 
                    and t1.Sequence  = t2.Sequence
                 ) ej1
  ON e.EmployeeID = ej1.EmployeeID
 
WHERE es.EmployeeStatus = 'Current' --AND (MIN(ej.SEQUENCE))

Open in new window

0
mooriginal
Asked:
mooriginal
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
DISTINCT keyword works only if ALL the columns have same value for more than one record. Means that if you won't have duplicates.
Check again the values and you'll find differences for the records that you think are duplicated.
Check if you don't need at least one join more.

NOTE: It's good rule to use INNER JOIN keyword instead of only JOIN.

Good luck
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>NOTE: It's good rule to use INNER JOIN keyword instead of only JOIN.
aha?...
0
 
mooriginalAuthor Commented:
yup I get the concept

I have data that is not duplicated as the difference is the ej.Postid field.
But thats why i tried the join with the sequence.

I wanted the 'sequence' field to make the postid unique by only showing position that was the most relevant.
So if there were 2 posts for an employee - it should use the MIN[sequence] to pick the decreasing number that than shows points to the position

But it doenst seem to be doing that and thats what im looking for help on ..
0
 
yuchingCommented:
try this

select DISTINCT
e.EmployeeID,
es.EmployeeStatus AS Status,
e.EmployeeDescNoID AS Emp_Name,
e.WindowsUserName AS LoginID,
e.DisplayEmployeeID AS UserID,
e.ExtNoUD AS Extension,
e.FFWMobileUD AS FFWMobile,
e.WorkEmail AS Email,
-- profitcentre
-- department
e.EliteNoUD AS SectionNumber,
e.SecretaryNameUD AS Secretary,
e.LocationUD AS Location,
ej1.PostID AS Position
 
FROM [Cascade].dbo.Employee e
JOIN [Cascade].dbo.Employee_Status es  ON e.EmployeeID = es.EmployeeID
LEFT OUTER JOIN (
         select t1.JobTitle, t1.EmployeeID, t1.PostID
         from [Cascade].dbo.EmployeeJobs t1
         join (
                    select employeeId, min(Sequence) as sequence
                    from [Cascade].dbo.EmployeeJobs
                    group by employeeID
            ) t2 on t1.employeeid = t2.employeeID   and t1.Sequence  = t2.Sequence
 ) ej1  ON e.EmployeeID = ej1.EmployeeID
 WHERE es.EmployeeStatus = 'Current' --AND (MIN(ej.SEQUENCE))
0
 
mooriginalAuthor Commented:
excellent
that worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now