How to create crosswalk table to match people with different id's?

Question

I have 2 tables with the same information including: id, name, address, zip code, phone number. However, The ID's are different for the same person on each table. How can I create a crosswalk table to match the id's based on the rest of the information?

EX)

Table1

ID   | Name     | Address     | Zip   | Phone
-----------------------------------------------------
1111 | John Doe | 123 Fake St | 12345 | 123-456-7890
-----------------------------------------------------

Table2

ID   | Name     | Address     | Zip   | Phone
-----------------------------------------------------
2222 | John Doe | 123 Fake St | 12345 | 123-456-7890
-----------------------------------------------------

Desired Crosswalk

Table1ID | Table2ID
---------------------
1111     | 2222
---------------------

Any insight would be greatly appreciated. Thanks!

Are you familiar with `JOIN`s? You can `JOIN` on all of the fields that you expect to match. — zimdanen, Nov 04 '13 at 23:25

score 4 · Accepted Answer · answered Nov 05 '13 at 00:10

4

You can join the two tables on the columns that match like:

SELECT Table1.ID AS Table1ID, Table2.ID AS Table2ID
FROM Table1
INNER JOIN Table2 ON Table1.Name = Table2.Name 
  AND Table1.Address = Table2.Address 
  AND Table1.Zip = Table2.Zip 
  AND Table1.Phone = Table2.Phone

Here I joined on all columns, you would have to adjust it to the ones that actually match...

answered Nov 05 '13 at 00:10

jpw

44,361
6
66
86

Very helpful, thank you. I didn't know you could use 'and' 'or' operators with joins. – user2954362 Nov 05 '13 at 15:55

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

As is explained in this article and comments, you can use JOIN to match all columns in both tables through table aliases:

SELECT t1.id Tab1Id, t2.id Tab2Id 
FROM t1 
INNER JOIN t2 on tb1.name = t2.name 
INNER JOIN t2 tb2_1 on t1.address = tb2_1.address 
INNER JOIN t2 tb2_2 on t1.zip = tb2_2.zip 
INNER JOIN t2 tb2_3 on t1.phone = tb2_3.phone

edited May 23 '17 at 12:30

Community

1
1

answered Nov 05 '13 at 00:14

Alejandro Quiroz

2,604
1
15
17

This is incorrect syntax; see **jpw**'s answer for the correct syntax. – zimdanen Nov 05 '13 at 00:17
Nice, @jpw answer is more simple and clear, i'm going to clean some views, tks! – Alejandro Quiroz Nov 05 '13 at 00:21

How to create crosswalk table to match people with different id's?

2 Answers2