Re: Database like "left join" or SAS like Merge function

*To*: mathgroup at smc.vnet.net*Subject*: [mg83599] Re: Database like "left join" or SAS like Merge function*From*: "Steve Luttrell" <steve at _removemefirst_luttrell.org.uk>*Date*: Sat, 24 Nov 2007 04:11:58 -0500 (EST)*References*: <fi6abc$oh3$1@smc.vnet.net>

Here is an example that does what you want: Define a matrix where the first two columns contain keys and the third column contains data. In[1]:= mat1=Table[{RandomInteger[3],RandomInteger[2],RandomReal[]},{10}] Out[1]= {{2,0,0.719448},{2,0,0.155021},{3,0,0.138765},{1,2,0.714609},{0,0,0.244554},{2,0,0.582359},{1,2,0.354337},{0,2,0.498536},{0,2,0.159724},{1,2,0.624066}} Similarly, define a second matrix. In[2]:= mat2=Table[{RandomInteger[3],RandomInteger[2],RandomReal[]},{20}] Out[2]= {{1,0,0.756742},{3,2,0.112279},{2,1,0.091024},{2,0,0.177684},{2,1,0.77082},{3,1,0.25392},{0,0,0.508483},{2,2,0.201832},{1,2,0.738323},{2,2,0.139091},{1,2,0.918997},{3,1,0.801789},{1,1,0.0767979},{1,2,0.600228},{3,0,0.129787},{1,1,0.699115},{2,1,0.897158},{3,2,0.364663},{2,0,0.487728},{2,1,0.901181}} Join the matrices, then sort the result by the key columns, then split the sorted result into subsets according to their key. In[3]:= Split[Sort[Join[mat1,mat2]],#1[[2]]==#2[[2]]&] Out[3]= {{{0,0,0.244554},{0,0,0.508483}},{{0,2,0.159724},{0,2,0.498536}},{{1,0,0.756742}},{{1,1,0.0767979},{1,1,0.699115}},{{1,2,0.354337},{1,2,0.600228},{1,2,0.624066},{1,2,0.714609},{1,2,0.738323},{1,2,0.918997}},{{2,0,0.155021},{2,0,0.177684},{2,0,0.487728},{2,0,0.582359},{2,0,0.719448}},{{2,1,0.091024},{2,1,0.77082},{2,1,0.897158},{2,1,0.901181}},{{2,2,0.139091},{2,2,0.201832}},{{3,0,0.129787},{3,0,0.138765}},{{3,1,0.25392},{3,1,0.801789}},{{3,2,0.112279},{3,2,0.364663}}} This is a very basic example, but it can readily be generalised. Steve Luttrell West Malvern, UK "Peter" <pjcrosbie at gmail.com> wrote in message news:fi6abc$oh3$1 at smc.vnet.net... > Is there a builtin function that can "join" (merge) two two- > dimensional data lists based on a single or multiple key column? > > This seems like such a basic requirement that I feel like I must be > missing something obvious. > > Thanks, Peter >