Solved

Duplicates from my join

Posted on 2008-10-09
5
198 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 46

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 35
Optimizing a query 3 34
Near realtime alert if SQL Server services stop. 20 56
SQL 2008 R2 calc date formula 3 26
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 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

15 Experts available now in Live!

Get 1:1 Help Now