Cross-database query example to join data from a different MSSQL database containing ZIP data using a CTE

city-state-zip-mssql-cte-join-example.sql

-- city-state-zip-mssql-cte-join-example.sql (Cross-database query example to join data from a different MSSQL database containing ZIP data using a CTE)
-- 2025-01-22
-- Author: James Moberg http://sunstarmedia.com  @sunstarmedia
-- GIST: https://gist.github.com/JamoCA/f082d8ca1b42f4d5474edf5e367a4df1
-- TWEET: https://x.com/gamesover/status/1882125235996254489
-- Using MSSQL with "U.S. ZIP Code Database (5 Digit)" data from https://www.zip-codes.com/zip-code-database.asp
-- NOTE: Some ZIPs may be assigned to one-or-more cities. For this use, only the primary (P) city record is returned.

WITH ZipCTE AS (
 SELECT ZipCode, CityAliasMixedCase AS City, State, TimeZone, TimeZoneOffset, DayLightSaving, Latitude, Longitude
 FROM ReferenceData..ZIPCodes
 WHERE PrimaryRecord = 'P'
)
SELECT U.ID, U.Email, Z.City, Z.State, U.ZIP, Z.TimeZone, Z.TimeZoneOffset, Z.DayLightSaving, Z.Latitude, Z.Longitude, U.DateAdded
FROM Users U LEFT OUTER JOIN ZipCTE Z ON Z.ZipCode = U.ZIP
ORDER BY U.DateAdded DESC;