?
Solved

Dups: Returning Survivor Record and single records

Posted on 2014-03-09
4
Medium Priority
?
419 Views
Last Modified: 2014-03-09
I have two tables Employees and Dups. The Dups table holds employee ids, what id they are a dup of, and which record is the survivor.  I want to join with the employees table, pull out the survivor record and the other employees that are singles.

Employee                         Dups
Emplid                              Curr_Emplid           Correct_Emplid      Survivor
1234                                  1234                       5678                   N
5678                                   5678                       5678                  Y
1122                                   1122                       5678                  N
1111
6765

So basically, employees 1234,5678, and 1122 are all the same person but the emplid with all the correct data is 5678.  Employees 1111 and 6765 do not have dups so their records are correct.


I want to query Employee and have it only return
Emplid
5678
1111
6765
0
Comment
Question by:klpayton
[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
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:slinkygn
ID: 39915956
I'm assuming you'll also need other fields from Employee, otherwise you'd need to only query the Dups table and not the Employee table...

SELECT e.Emplid FROM Employees e, Dups d WHERE e.Emplid = d.Emplid AND (d.Emplid = d.Correct_Emplid OR d.Emplid = '')

or

SELECT e.Emplid FROM Employees e, Dups d WHERE e.Emplid = d.Emplid AND (d.Emplid = d.Correct_Emplid OR d.Emplid IS NULL)

same thing in Oracle.
0
 

Author Comment

by:klpayton
ID: 39915970
thanks slinkygyn. I need to query from Employee because there are other emps in that table that are single records, not dups. I need to return from the employees table the survivor record of the duplicate emps and the employees that do not have dups.
0
 

Author Comment

by:klpayton
ID: 39915997
Im not understanding your query: SELECT e.Emplid FROM Employees e, Dups d WHERE e.Emplid = d.Emplid AND (d.Emplid = d.Correct_Emplid OR d.Emplid IS NULL)



There isnt a column called emplid in Dups only Curr_emplid, Correct_emp, and survivor. Which column are you referring to?
0
 
LVL 6

Accepted Solution

by:
slinkygn earned 2000 total points
ID: 39916006
Gotcha, wasn't clear on the column layout you had up there.  I thought both tables had Emplid... that makes the query much different.

SELECT Emplid FROM Employees LEFT JOIN Dups ON Emplid = Curr_Emplid WHERE Curr_Emplid = Correct_Emplid OR Correct_Emplid IS NULL
0

Featured Post

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

752 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