Solved

Dups: Returning Survivor Record and single records

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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