How to join a table and replace a numeric zip code

问题:

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 to join it with another table zip_codes that has these same columns as well as an additional column, city_name, that has the text string of the city associated with each zip.

I want to replace the numeric values on the first table with the names of the cities on the second table. So for instance, if I have a customer_id in 'main_cohort' that has a 'zip_value3' of 100, I want to replace that in my output with the value from the city_name from the zip_code table, which would be New York.

回答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

 1              100         10000       New York
 2              200         20000       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

回答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