Solved

Duplicates from my join

Posted on 2008-10-09
5
197 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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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 142

Expert Comment

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

Author Comment

by:mooriginal
Comment Utility
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
Comment Utility
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
Comment Utility
excellent
that worked
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now