Dups: Returning Survivor Record and single records

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
klpaytonAsked:
Who is Participating?
 
slinkygnConnect With a Mentor PresidentCommented:
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
 
slinkygnPresidentCommented:
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
 
klpaytonAuthor Commented:
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
 
klpaytonAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.