Determining a shipping zone from a zip code. How to unify those two tables

Transact-SQL
1

I have two tables. The first is the USPS Zone table. If the first three digits of a zip code fit within the Min/max range, teh zone is the value in the zone field.

For instance, one zip code on the orders list is 07002. So I extract the first 3 digits and it is 070, which is seen as 70 because it is numeric. The other table has a row with a Min of 068 and a max of 098, which is a perfect fit. It would then receive the zone #4 since this zip code is within that range.

My issues is that I do not know how to unify these two tables. They don't have a common key.

CREATE TABLE #USPS_Zone(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Min] [int] NULL,
	[Max] [int] NULL,
	[Zone] [int] NULL
) ON [PRIMARY]

INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('5','5','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('66','66','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('67','67','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('242','242','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('243','243','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('244','244','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('245','245','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('254','254','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('260','260','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('265','265','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('266','266','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('267','267','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('268','268','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('294','294','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('297','297','1')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('298','298','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('299','299','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('340','340','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('344','344','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('375','375','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('399','399','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('410','410','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('427','427','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('612','612','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('635','635','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('677','677','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('678','678','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('679','679','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('733','733','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('739','739','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('765','765','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('770','770','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('820','820','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('821','821','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('833','833','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('834','834','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('863','863','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('864','864','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('865','865','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('969','969','9')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('6','9','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('10','65','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('68','98','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('100','119','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('120','123','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('124','127','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('128','129','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('130','199','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('200','205','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('206','212','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('214','219','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('220','239','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('240','241','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('246','253','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('255','259','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('261','264','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('270','277','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('278','279','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('280','282','1')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('283','293','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('295','296','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('300','307','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('308','309','2')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('310','312','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('313','317','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('318','319','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('320','329','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('330','333','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('334','339','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('341','342','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('346','347','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('349','352','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('354','372','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('373','374','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('376','379','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('380','398','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('400','402','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('403','409','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('411','418','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('420','424','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('425','426','3')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('430','495','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('496','509','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('510','513','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('514','516','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('520','528','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('530','532','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('534','535','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('537','551','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('553','564','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('565','567','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('570','577','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('580','588','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('590','597','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('598','599','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('600','611','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('613','620','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('622','631','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('633','634','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('636','639','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('640','641','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('644','658','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('660','662','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('664','676','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('680','681','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('683','687','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('688','693','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('700','701','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('703','708','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('710','714','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('716','722','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('723','724','4')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('725','731','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('734','738','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('740','741','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('743','764','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('766','767','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('768','769','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('772','778','5')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('779','797','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('798','799','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('800','812','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('813','816','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('822','823','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('824','832','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('835','838','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('840','847','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('850','853','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('855','857','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('859','860','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('870','871','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('873','880','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('881','882','6')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('883','885','7')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('889','891','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('893','895','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('897','898','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('900','908','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('910','928','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('930','968','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('970','986','8')
INSERT INTO #USPS_Zone ([Min],[Max],[Zone]) VALUES ('988','999','8')



CREATE TABLE #OrderList(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OrderID] NVARCHAR(40) NULL,
	[ZipCode] NVARCHAR(40) NULL
) ON [PRIMARY]

INSERT INTO #OrderList ([OrderID],[ZipCode]) VALUES ('123456','11235')
INSERT INTO #OrderList ([OrderID],[ZipCode]) VALUES ('123457','10001')
INSERT INTO #OrderList ([OrderID],[ZipCode]) VALUES ('123458','90210')
INSERT INTO #OrderList ([OrderID],[ZipCode]) VALUES ('123459','07002')

select * from #USPS_Zone
select * from #OrderList
2

Try this:

select *
  from #OrderList as o
       left outer join #USPS_Zone as z
               on z.[Min]<=substring(o.ZipCode,1,3)
              and z.[Max]>=substring(o.ZipCode,1,3)
;

or this:

select *
  from #OrderList as o
       left outer join #USPS_Zone as z
               on z.[Min]<=cast(substring(o.ZipCode,1,3) as int)
              and z.[Max]>=cast(substring(o.ZipCode,1,3) as int)
;
3

Thank you!

Looking at how you did it; you used concepts I know, but more creatively. I appreciate the help

4

Even shorter and possible faster:

select *
  from #OrderList as o
       left outer join #USPS_Zone as z
               on substring(o.ZipCode,1,3) between z.[Min] and z.[Max]
;

or this:

select *
  from #OrderList as o
       left outer join #USPS_Zone as z
               on cast(substring(o.ZipCode,1,3) as int) between z.[Min] and z.[Max]
;
5

Btw, be sure to add the appropriate clustering to #USPS_Zone, just in case. And you can get rid of the identity, as you already have a perfect unique key here:

CREATE TABLE #USPS_Zone(
	[Min] [int] NULL,
	[Max] [int] NULL,
	[Zone] [int] NULL,
        CONSTRAINT USPS_Zone__CL UNIQUE CLUSTERED ( Min, Max ) ON [PRIMARY]
)