Solved

Dups: Returning Survivor Record and single records

Posted on 2014-03-09
4
414 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
  • 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

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

16 Experts available now in Live!

Get 1:1 Help Now