databasesqliteorganization

How to join tables when entries don't exactly match

I have two tables I need to join based on zip code.

Table1:

ZIP INCOME
ZCTA5 01845 45,000
ZCTA5 11421 52,000
... ...
ZCTA5 99704 42,000

There are thousands of entries in Table1

Table2:

ZIP column1
01845 Y
11421 N
... ...
99704 Y

Table1 has several more entries in it than Table2 that I don't need, I'm just trying to get incomes for the zip codes listed in Table2. I figure I can just do an inner join for this, but I'm running into the issue that I have that ZCTA5 preceding the zip code in table1. I was curious if there was a way to do this?

Solution

  • Use the substr function to extract the zip code from table1, and join on that:

    SELECT * FROM table1 t1 INNER JOIN table2 t2 ON substr(t1.zip, -5) = t2.zip;