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?
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