Solved

Duplicates from my join

Posted on 2008-10-09
5
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50

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 500 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

Independent Software Vendors: 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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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