insert value from join with If/Case from two tables, into a third table

So I have the following issue in creating a sql query.

A linked server so am using an open query. Two tables as per picture below:

I need to insert value into a third table, the value to insert to the third table comes from the two tables above which I join via KEY ID.

However, in the join I want to choose all columns from table A and only Value Code from table B (there are multiple value codes per Key ID in table B) so need an if or case depending on the following:

If/Case Table B has Code = PRODUCT, choose VALUE CODE A,

If there is no Code=PRODUCT, then chose VALUE CODE from code = FOO, in the case of KEY ID 2, that would be VALUE CODE D.

If there is no Code = PRODUCT or no Code = FOO for the given Key ID, then choose VALUE CODE that corresponds to Code = BAR, in this case of Key ID 3 it would be VALUE CODE F.

How do I write this sql query of join with if/case in a proper way?