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. (edit update: SQL Server) 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.

Update: Sometimes Foo can come before Product in table B, also if there is no correspondence, say there is neither product, foo or bar I wish to return blank in the column row.

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

Answer

Not sure what platform you are using, but here is how I would do it in SQL Server

SQL Server Method

Select a.KeyID,a.[TimeStamp],a.SalesAmount,b.ValueCode
From TableA As a
Outer Apply (
            /*This will grab the top 1 KeyID match, with the sorting you specified based on the code in TableB*/
            Select Top (1) *
            From TableB As tb
            Where a.KeyId = tb.KeyID
            And tb.Code In ('Product','Foo','Bar') /*If only want codes matching the 3 values, then include this line*/
            Order By 
                Case 
                    When tb.Code = 'Product' Then 1
                    When tb.Code = 'Foo' Then 2
                    When tb.Code = 'Bar' Then 3
                    Else 4 /*Pick any other values last*/
                End
        ) As b