samedi 27 juin 2015

Matching columns - in a Query

Trying to see if I can build some logic around matching these columns. I have 2 tables

        Table A
    Make             Model
    BMW              2 Series
    BMW              3 Series
    BMW              3 Series GT
    BMW              5 Series
    BMW              5 Series GT
    BMW              7 Series
    Rolls-Royce      Other
    Mini             Cooper
    Mini             Coupe 


        Table B
    Make           Series
    BMW            2 Series
    BMW            3 Series
    BMW            5 Series
    Bavarian Motor 7 Series
    Rolls-Royce    42000 GT
    Mini           Cooper

I need extract the output as below and store in a table

    Output
    Make              Model_A        Model_B
    BMW              2 Series        2 Series
    BMW              3 Series        3 Series
    BMW              3 Series GT     3 Series
    BMW              5 Series        5 Series
    BMW              5 Series GT     5 Series
    BMW              7 Series        7 Series
    Rolls-Royce      Other           42000 GT  
    Mini             Cooper          Cooper
    Mini             Coupe           Cooper

For the most part, I could match the columns using the query below:

        ( A.SERIES like '%' + B.model + '%'
         OR B.model like '%' + A.series + '%' )

But this does not handle the "Coupe" vs "Cooper", Rolls-Royce nor the "BMW" vs "Bavarian Motor" cases.

Any idea how I can handle all these in T-sql or a query? Soundex nor any distance algorithm works in this instance - tried both.

I know I can handle all cases using a "union all" to run in a single query, but the key is to handle the above edge cases.

Thanks in advance for your help. ~Bee

Aucun commentaire:

Enregistrer un commentaire