Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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).

share|improve this question
1  
Why not use the Mma DatabaseLink package(reference.wolfram.com/mathematica/DatabaseLink/tutorial/…) and SQLExecute to execute any SQL command directly. –  kguler Mar 7 '12 at 9:48
    
yes i tried it, the thing is the data I need to access are saved in a Access Database.. somehow I couldn't establish a link to the ms access directly... –  PeriodicProgrammer Mar 7 '12 at 10:22
    
Access access (hoho) can be tricky, especially with 64bit systems. On a Win XP 32bit system I had no difficulties (worked as advertised, at least for reading). You just have to get the whole ODBC/DSN registration mumbo jumbo right. –  Yves Klett Mar 7 '12 at 13:08
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.