07-31-2014 10:53 PM
Hi. I'm trying to link 1 field from one table to many fields form another table. Here's what I mean. See below the data first.
The final result Table 3, I would like to link/merge each Product_code in Table_1 to each of the Product_codes in Table_2 if the first 3 digits are the same.
Here's how the final table would look:
Product_code_1 Product_code_2 Variable_1_1 Variable_1_2
V3B_A V3B_1 34 24
V3B_A V3B_2 34 87
V3B_A V3B_3 34 19
V3B_B V3B_1 67 24
V3B_B V3B_2 67 87
V3B_B V3B_3 67 19
V5T_A V5T_1 12 08
V5T_A V5T_2 12 03
V5T_A V5T_3 12 24
V5T_B V5T_1 11 08
V5T_B V5T_2 11 03
V5T_B V5T_3 11 24
But this is just a short sample. I actually have many product codes and many more variables.. I don't assume it should matter. Or is it as simple as just stripping the first 3 digits from both tables and merge?
07-31-2014 11:21 PM
select a.product_code as product_code1,b.product_code as product_code2,
a.variable_1 as variable_1_1,b.variable_1 as variable_1_2 from have1 a, have2 b
07-31-2014 11:32 PM
You can get the Table3 easily by sql procedure using Cartesian product as follows.
create table Table3 as select a.Product_code as Product_code_1,
b.Product_code as Product_code_2,
a.Variable_1 as Variable_1_1,
b.Variable_1 as Variable_1_2
from Table1 as a, Table2 as b
08-01-2014 02:21 AM
Do not go for a cartesian product. Yes it will solve logical all combinations. The cost is that it is not practical for using needing tremendous resources when using some more larger datasets. It is the one to be avoided learning dbms.
Google for "table lookup" as that it what you described.