joining one column from a table to another while matching all other columns

hello,

i have two tables a and b

a= Department Name ID

k Joe 12

l Laura 234

m Mike 45

b Tom 23

j Ash 55

b= Department Name ID Gender

l May 222 F

l Laura 234 F

m Gary 45 M

j Ash 55 M

a=table({['k','l','m','b','j'],['Joe','Laura','Mike','Tom','Ash'],[12,234,45,23,55]},'VariableNames',{'Department','name','ID'});

b=table({['l','l','m','j'],['May','Laura','Gary','Ash'],[222,234,45,55],['F','F','M','M']},'VariableNames',{'Department','name','ID','Gender'});

I want to join b on a when all Departmen, ID and Name match with the resultant only showing these three columns once and adding a fourth coulum that has Gender as follows

ans== Department Name ID Gender

k Joe 12 Nan

l Laura 234 F

m Mike 45 Nan

b Tom 23 Nan

j Ash 55 M

l May 222 Nan

m Gary 45 Nan

Answer 1

[the cyclist]

(Your code won't produce the tables a and b.)

I believe the outerjoin command does what you want:

a = table({'k','l','m','b','j'}',{'Joe','Laura','Mike','Tom','Ash'}',[12,234,45,23,55]', ...
         'VariableNames',{'Department','name','ID'});

b = table({'l','l','m','j'}',{'May','Laura','Gary','Ash'}',[222,234,45,55]',{'F','F','M','M'}', ...
         'VariableNames',{'Department','name','ID','Gender'});

outerjoin(a,b,'MergeKeys',true)

[ma sd]

you are right my code up there would not work for creating the tables

but I wanted the tables to be made of multiple rows and one column per header so the way to write the table would be

a=table({'j' ; 'l' ;.....});

thank you the outer join worked perfectly