Databases
StackOverflow2013
Postsdbo
POHow to match people between separate systems using SQL?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to match people between separate systems using SQL?
    primarykey
    data
    text
    <p>I would like to know if there is a way to match people between two separate systems, using (mostly) SQL. </p> <p>We have two separate Oracle databases where people are stored. There is no link between the two (i.e. cannot join on person_id); this is intentional. I would like to create a query that checks to see if a given group of people from system A exists in system B.</p> <p>I am able to create tables if that makes it easier. I can also run queries and do some data manipulation in Excel when creating my final report. I am not very familiar with PL/SQL. </p> <p>In system A, we have information about people (name, DOB, soc, sex, etc.). In system B we have the same types of information about people. There could be data entry errors (person enters an incorrect spelling), but I am not going to worry about this too much, other than maybe just comparing the first 4 letters. <a href="https://stackoverflow.com/questions/988050/matching-records-based-on-person-name">This question deals with that problem more specifically</a>.</p> <p>They way I thought about doing this is through correlated subqueries. So, roughly,</p> <pre><code>select a.lastname, a.firstname, a.soc, a.dob, a.gender case when exists (select 1 from b where b.lastname = a.lastname) then 'Y' else 'N' end last_name, case when exists (select 1 from b where b.firstname = a.firstname) then 'Y' else 'N' end first_name, case [etc.] from a </code></pre> <p>This gives me what I want, I think...I can export the results to Excel and then find records that have 3 or more matches. I believe that this shows that a given field from A was found in B. However, I ran this query with just three of these fields and it took over 3 hours to run (I'm looking in 2 years of data). I would like to be able to match on up to 5 criteria (lastname, firstname, gender, date of birth, soc). Additionally, while soc number is the best choice for matching, it is also the piece of data that tends to be missing the most often. What is the best way to do this? Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJulie
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to match people between separate systems using SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to match people between separate systems using SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI would advise to use joins rather than the subqueries, just as HLGEM and rexem did. Additionally, if you did not already do so, you could copy the data into one database to increase performance.
      singulars
      1. POHow to match people between separate systems using SQL?
      1. USJuergen Hartelt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload