Solved

Dups: Returning Survivor Record and single records

Posted on 2014-03-09
4
415 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

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

776 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