My T1:
ROUTE_NAME
ASE DTW
BLI DTW
DTW MOD
DTW OGG
DTW VPS
DTW LAS
T2 is the lookup table which has two columns:
airp_cd city_cd
UPP UPP
MUF MUF
PPU PPU
CGV CGV
DTW DTT
I'd like to get the city pair by looking up the airp_cd in T2, and able to write an Excel formula for it, City Pair = VLOOKUP(LEFT(A2,3),T2!$A$1:$B$6,2,0)&" "&VLOOKUP(RIGHT(A2,3),T2!$A$1:$B$6,2,0). Here is the expected result:
ROUTE_NAME City pair
ASE DTW ASE DTT
BLI DTW BLI DTT
DTW MOD DTT MOD
DTW OGG DTT OGG
DTW VPS DTT VPS
DTW LAS DTT LAS
How can I write the sql for equivalent? I tried with:
SELECT
T1.ROUTE_NAME,
T2.city_cd
FROM T1
LEFT OUTER JOIN T2
ON (LEFT(T1.ROUTE_NAME,3) = T2.airp_cd AND RIGHT(T1.ROUTE_NAME,3) = T2.airp_cd)
But it's not the expected results.
Logically you are doing two separate lookups. So you can't use AND
in a single join criteria - that would require that BOTH the left side AND the right side be equal to the same airp_cd
. You would still be in a pickle if you tried OR
instead - that would just mean only the right or left sides need to match, but you really want the left side to match (for the first lookup), and separately you also want the right side to match (for the second lookup).
So to make this work, you can separate the two lookups by using your T2 table twice, with aliases to represent the two lookups that they represent.
Note: this is written for MS SQL SERVER (although it is fairly vanilla SQL except maybe the syntax for temp tables and the datatypes) - so you may need to alter as needed for your database system.
CREATE TABLE #T1 (Route_Name NVARCHAR(30));
INSERT INTO #T1 VALUES
('ASE DTW'),
('BLI DTW'),
('DTW MOD'),
('DTW OGG'),
('DTW VPS'),
('DTW LAS');
CREATE TABLE #T2 (airp_cd NVARCHAR(30), city_cd NVARCHAR(30));
INSERT INTO #T2 VALUES
('UPP', 'UPP'),
('MUF', 'MUF'),
('PPU', 'PPU'),
('CGV', 'CGV'),
('DTW', 'DTT'),
('ASE', 'ASE'),
('BLI', 'BLI'),
('MOD', 'MOD'),
('VPS', 'VPS'),
('LAS', 'LAS'),
('OGG', 'OGG');
SELECT
T1.Route_Name,
COALESCE(RouteName1.city_cd, '')
+ ' '
+ COALESCE(RouteName2.city_cd, '') AS City_Pair
FROM
#T1 T1
LEFT OUTER JOIN #T2 AS RouteName1
ON LEFT(T1.Route_Name,3) = RouteName1.airp_cd
LEFT OUTER JOIN #T2 AS RouteName2
ON RIGHT(T1.Route_Name,3) = RouteName2.airp_cd;
ROUTE_NAME | City_Pair |
---|---|
ASE DTW | ASE DTT |
BLI DTW | BLI DTT |
DTW MOD | DTT MOD |
DTW OGG | DTT OGG |
DTW VPS | DTT VPS |
DTW LAS | DTT LAS |
PLEASE NOTE - using functions like Left()
and Right()
in your join criteria will almost certainly result in bad performance if you have a significant amount of data.
Table T1 really should be separated into a two-column table.
I hope this helps.