Merge multiple tables with three keys

I have three tables with the same categories of data. All three tables have these three keys - "Outcome","Range","ROI". There are 4 possible outcomes, 8 possible ranges and 68 possible ROIs (all ranges and all ROIs can be within any of the four outcomes). I want to merge them using these three keys. I tried outerjoin which works for two but not three simultaneously. I tried merging two first and then adding the third to the new table (of the first two joined together). The issue with this approach is that I have to choose which of the first two table keys I want to use in the new merged table. So if I choose the first table keys this is not a problem for rows where table 1 and 2 have the same data or table 3 and table 1 have the same data. But for rows where table 3 and table 2 have the same data and table 1 does not then the data here is not matched (because I used keys matching first and third table). Is the a way to do this? Possibly by making a master table where all possible variatons are included and then matching the three tables to this master key?

Example:
Outcome = ["11_right", "12_right"]';
Range = ["80ms,110ms" "110ms,140ms", "140ms,195ms"]';
ROI = ["Red", "Yellow", "Blue, "Green"];

Answer 1

[Lei Hou]

Hi David,

I'm not sure whether I understand your problem correctly. Based on my understanding, outerjoin should help you with your use case. outerjoin will return the union of the unique combinations of the three keys from the input tables. Therefore, no matter whether the key combination of the right table exists in the left table or not, the output will contain all combinations with filling missing value for the rows that the combination was not found.

outcome1 = ["11_right"; "12_right"];
range1 = ["80ms,110ms";"110ms,140ms"];
ROI1 =  ["Red"; "Yellow"];
Var1 = [1.1;1.2];
t1 = table(outcome1,range1,ROI1,Var1,'VariableNames',{'Outcome', 'Range', 'ROI', 'Var1'});

outcome2 = ["11_right"; "12_right"];
range2 = ["140ms,195ms";"110ms,140ms"];
ROI2 =  ["Red"; "Yellow"];
Var2 = [2.1;2.2];
t2 = table(outcome2,range2,ROI2,Var2,'VariableNames',{'Outcome', 'Range', 'ROI', 'Var1'});

outcome3 = ["11_left"; "12_left"];
range3 = ["110ms,140ms";"140ms,195ms"];
ROI3 =  ["Blue"; "Green"];
Var3 = [3.1;3.2];
t3 = table(outcome3,range3,ROI3,Var3,'VariableNames',{'Outcome', 'Range', 'ROI', 'Var1'});

>> t1

t1 =

  2×4 table

     Outcome          Range          ROI       Var1
    __________    _____________    ________    ____

    "11_right"    "80ms,110ms"     "Red"       1.1 
    "12_right"    "110ms,140ms"    "Yellow"    1.2 

>> t2

t2 =

  2×4 table

     Outcome          Range          ROI       Var1
    __________    _____________    ________    ____

    "11_right"    "140ms,195ms"    "Red"       2.1 
    "12_right"    "110ms,140ms"    "Yellow"    2.2 

>> t3

t3 =

  2×4 table

     Outcome         Range          ROI      Var1
    _________    _____________    _______    ____

    "11_left"    "110ms,140ms"    "Blue"     3.1 
    "12_left"    "140ms,195ms"    "Green"    3.2 

>> t_1 = outerjoin(t1,t2,'Keys',{'Outcome', 'Range', 'ROI'},'MergeKeys',true)

t_1 =

  3×5 table

     Outcome          Range          ROI       Var1_t1    Var1_t2
    __________    _____________    ________    _______    _______

    "11_right"    "140ms,195ms"    "Red"         NaN        2.1  
    "11_right"    "80ms,110ms"     "Red"         1.1        NaN  
    "12_right"    "110ms,140ms"    "Yellow"      1.2        2.2  

>> t_2 = outerjoin(t_1,t3,'Keys',{'Outcome', 'Range', 'ROI'},'MergeKeys',true)

t_2 =

  5×6 table

     Outcome          Range          ROI       Var1_t1    Var1_t2    Var1
    __________    _____________    ________    _______    _______    ____

    "11_left"     "110ms,140ms"    "Blue"        NaN        NaN      3.1 
    "11_right"    "140ms,195ms"    "Red"         NaN        2.1      NaN 
    "11_right"    "80ms,110ms"     "Red"         1.1        NaN      NaN 
    "12_left"     "140ms,195ms"    "Green"       NaN        NaN      3.2 
    "12_right"    "110ms,140ms"    "Yellow"      1.2        2.2      NaN