How to match two sets of data over 1 or more identifier, of unequal length?

Question

Brief Question about how to best match lists with Criteria from Identifiers (thus for me: translate SQL to Mathematica ;) ).

If I have two sets:

 set1 = {{ExperimentCode1, 
  No of Experiments1}, {ExperimentCode2, No of Experiments2} ...}  
 set2 = {{ExperimentCode1, 
   TypeofExp1}, {ExperimentCode2, No of TypeofExp1} ...}  

Now set 1 has 10 Entries, set2 only contains data for 3.

'Sql: Select * from set1 Inner Join set 2 on set1.Experimentcode=set2.Experimentcode'  

This only matches cases in SQL where the variables exist in both lists (Thus result = 3 Elements)

'Sql: Select * from set1 Left Join set 2 on set1.Experimentcode=set2.Experimentcode'  

This returns all Elements from List 1 and the matched from List 2 (result=10 elements, 3 Elements have now the additional value type, the other 7 Null values for type).

I think this is even more tricky when there is more than one Identifier (Thus two fields together represent a unique identfying number).

Answer 1

I don't know sql and the formats for set1 and set2 are somewhat unclear to me so I could be completely missing the point but maybe you could do something like this for the Inner Join query

intersect[set1_, set2_] := Module[{expCodes},
  expCodes = Intersection[set2[[All, 1]], set1[[All,1]]];
  Reap[Sow[#2, #1] & @@@ set1;
     Sow[#2, #1] & @@@ set2, expCodes, Flatten[{#1, #2}, 1] &][[2, 
     All, 1]]]

and this for the Left Join query

join[set1_, set2_] := Module[{expCodes},
  expCodes = set1[[All, 1]];
  PadRight[Reap[Sow[#2, #1] & @@@ set1;
     Sow[#2, #1] & @@@ set2, expCodes, Flatten[{#1, #2}, 1] &][[2, 
     All, 1]],
   Automatic, Null]]

Example data:

set1 = Table[{i, RandomInteger[10, 3]}, {i, 10}]
set2 = Table[{i, "Type " <> ToString[i]}, {i, RandomSample[Range[10], 3]}]

{{1, {9, 0, 4}}, {2, {3, 9, 4}}, {3, {9, 2, 5}}, {4, {1, 10, 5}}, 
   {5, {6, 4, 6}}, {6, {2, 1, 4}}, {7, {6, 5, 6}}, {8, {7, 3, 1}}, 
   {9, {9, 4, 9}}, {10, {6, 6, 2}}}

{{5, "Type 5"}, {3, "Type 3"}, {7, "Type 7"}}

Then intersect gives

intersect[set1, set2]

{{3, {9, 2, 5}, "Type 3"}, {5, {6, 4, 6}, "Type 5"}, {7, {6, 5, 6}, "Type 7"}}

and join

join[set1, set2]

{{1, {9, 0, 4}, Null}, {2, {3, 9, 4}, Null}, {3, {9, 2, 5}, "Type 3"}, 
   {4, {1, 10, 5}, Null}, {5, {6, 4, 6}, "Type 5"}, {6, {2, 1, 4}, Null}, 
   {7, {6, 5, 6}, "Type 7"}, {8, {7, 3, 1}, Null}, {9, {9, 4, 9}, Null}, 
   {10, {6, 6, 2}, Null}}

This should be expandable to selection on multiple columns by adjusting the definition of expCodes in the definitions of intersect and join.