SQL question: vlookup equivalent in SELECT

Question

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.

Answer 1

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.

sample data

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');

query

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;

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

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.