sql server 2014 - SQL query -- no consistent key -- search multiple columns in one table based upon value in another -

i trying join products table prod_ref table based upon multiple conditions. problem there isn't consistent key join these 2 tables.

the upc code unique each item in prod_ref, there more 1 match in products table (or no match @ all). , want test match on columns upc1, upc2, upc3, upc4, upc5, , upc6 products table upc column in prod_ref.

it gets further complicated because (not all) of upc codes in prod_ref table have leading zero. need consider if "upc n" codes products "contained in" upc code in prod_ref. if no match can found bump down consider combination manufacturer name , part # , hope match.

this gets little tricky in there partial match manufacturer name, exact match part #. (but, more 1 manufacturer have same part number associated product.)

so, want me tackle beast? :) in advance.

• the prod_ref table ~ 3million rows.
• thee products table ~ 16k rows.

we can put indexes , such wherever want.

[products]

 id upc 1       upc 2       upc 3       upc 4   upc 5   upc 6   manufacturer        manufacturer part #  1  78698502014 7869850201  78698404301 null    null    null    radiator specialty  eb1  16 78698718101 7869845980  78698459806 null    null    null    radiator specialty  gr1  17 45408020007 4540802000  null        null    null    null    maasdam             144sb-6  18 45408010008 4540801000  null        null    null    null    maasdam             144s-6 1  19 79843002137 7757801495  null        null    null    null    frost king          3a59  27 36448124640 3644812464  2906970464  null    null    null    franklin electric p 11010y152 

[prod_ref]

 item_pik   upc             mfr_fullname        mfr_cat_num     mfr_description     product_name  510        78698502014     radiator specialty  eb1             submersible         pump <-- 1*  512        78698459806     radiator specialty  gr1             submersible         pump <-- 2*  513        045408020007    maasdam             144sb-6         dual purpose        pump <-- 3*  516        7757801495      frost king /  3a59            dual purpose        pump <-- 4* 

• (*1) match upc 1
• (*2) match upc 3
• (*3) upc 1 contained in (leading zeros)
• (*4) if no upc match, match manufacturer (full or partial name match) , part #

just add little more context. left out few details on prod_ref table -- include description details, image_url, , other items.

the output ideally following:

      id    item_pik    upc 1       upc 2       upc 3       upc 4   upc 5   upc 6   mfr_fullname        product_name    mfr_cat_num     description_details     image_url       1     510         78698502014 7869850201  78698404301 null    null    null    radiator specialty  pump            eb1             blah, blah, blah        www.abc.com/images/123abc.jpg       16    512         78698718101 7869845980  78698459806 null    null    null    radiator specialty  pump            gr1             blah, blah, blah        www.abc.com/images/456abc.jpg       17    513         45408020007 4540802000  null        null    null    null    maasdam             pump            144sb-6         blah, blah, blah        www.abc.com/images/789abc.jpg 

or, happy single column upc code match (if there one); somthing like:

      id    item_pik    upc         mfr_fullname        product_name    mfr_cat_num     description_details     image_url       1     510         78698502014 radiator specialty  pump            eb1             blah, blah, blah        www.abc.com/images/123abc.jpg       16    512         78698718101 radiator specialty  pump            gr1             blah, blah, blah        www.abc.com/images/456abc.jpg       17    513         45408020007 maasdam             pump            144sb-6         blah, blah, blah        www.abc.com/images/789abc.jpg 

might not best or pretty solution, thinking this? first part @ least.

select   id   ,item_pik   ,upc1   ,upc2   ,upc3   ,upc4   ,upc5   ,upc6   ,mfr_fullname   ,prodcut_name   ,mfr   ,case when b.product_name null       case when c.product_name null          case when d.product_name null               ..          else              d.product_name          end       else         c.product_name       end      else       b.product_name     end [product_name]   products   left join [prod_ref] b on (a.upc1 = b.upc)   left join [prod_ref] c on (a.upc2 = c.upc)   left join [prod_ref] d on (a.upc3 = d.upc)   left join [prod_ref] e on (a.upc4 = e.upc)   left join [prod_ref] f on (a.upc5 = f.upc)   left join [prod_ref] g on (a.upc6 = g.upc)