[SOLVED] Build a crosswalk table in SQL when no key or index to join on

Build a crosswalk table in SQL when no key or index to join on

I've been provided a list of products names (50-100 names each quarter) and I need to build a crosswalk table from these broad product names to more specific names that are stored in our product table.

Here is an example of two products I've been provided with:

Table A:
FLOVENT
ASMANEX

My product names above were written to a temp table (Table A) and have no keys or indexes to perform joins to the product table which contains all the variations of the products I wish to pull back from the DB.

When I look in the product table (Table B) using the following query:

SELECT *
FROM
PROD_TABLE AS B
WHERE B.BRAND_NM LIKE '%FLOVENT%' OR B.BRAND_NM LIKE '%ASMANEX%'

I get the following variants:

ASMANEX HFA                   
ASMANEX TWISTHALER 120 METERED
ASMANEX TWISTHALER 30 METERED 
ASMANEX TWISTHALER 60 METERED
FLOVENT DISKUS                
FLOVENT HFA

I wish to avoid using case statements as my list of products in Table A can be 50-100 long and gets updated each quarter, hence my aversion to using case statements.

How can I build a crosswalk table that takes the original SQL query inputs in Table A and shows the detailed product break out from the PROD_TABLE (Table B), is this even possible?

My table would look something like follows:

ORIGINAL_VALUE MATCHED_VALUES
FLOVENT        FLOVENT DISKUS
FLOVENT        FLOVENT HFA
ASMANEX        ASMANEX HFA
ASMANEX        ASMANEX TWISTHALER 120 METERED
ASMANEX        TWISTHALER 30 METERED 
ASMANEX        TWISTHALER 60 METERED

I think what I am trying to achieve is a fuzzy matching join if this is possible in SQL.

Solution

You can use the join with fixed value as follows:

SELECT D.PRODUCT as match, 
       P.*
FROM PROD_TABLE P 
JOIN (SELECT 'FLOVENT' PRODUCT FROM DUMMY
      UNION
      SELECT 'ASMANEX' PRODUCT FROM DUMMY) D 
ON P.BRAND_NM LIKE '%' + D.PRODUCT + '%'