How To Join A Table And Replace A Numeric Zip Code Value With A Text Zip Code Value

I have one table main_cohort that has two different columns which contain zip codes in 3 digit and 5 digit numeric form (e.g. 100 or 10017), zip_value3 and zip_value5, and I want t

Solution 1:

# Table - tbl_MainCohort #

CustomerID Zip_Value3 Zip_Value5

  1             100         10000
  2             200         20000

Table - tbl_ZipCodes

ZipID Zip_Value3 Zip_Value5 City_Name

110010000New York
 220020000       California

The following query will give you all Cities for all the ZipCodes in the list where both ZipCode columns from both the tables match.

SELECT City_Name from tbl_ZipCodes codes INNER JOIN tbl_MainCohort main ON codes.Zip_Value3 = main.Zip_Value3 AND codes.Zip_Value5 = main.Zip_Value5

Solution 2:

I am guessing that you want one city, first checking the 5-digit zip code and then the 3-digit one:

select mc.*, coalesce(zc5.city_name, zc3.city_name) as city_name
from main_cohort mc left join
     zip_code zc5
     on mc.zip_value5 = zc5.zip_code left join
     zip_code zc3
     on mc.zip_value3 = zc3.zip_code