teradatateradata-sql-assistantteradatasql

SQL question: vlookup equivalent in SELECT


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.


Solution

  • 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.