?
Solved

Duplicates from my join

Posted on 2008-10-09
5
Medium Priority
?
223 Views
Last Modified: 2012-05-05
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
Comment
Question by:mooriginal
5 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 22680183
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22680468
>NOTE: It's good rule to use INNER JOIN keyword instead of only JOIN.
aha?...
0
 

Author Comment

by:mooriginal
ID: 22685306
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
 
LVL 11

Accepted Solution

by:
yuching earned 2000 total points
ID: 22685576
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
 

Author Comment

by:mooriginal
ID: 22686089
excellent
that worked
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question