8
$\begingroup$

I think my problem is pretty simple, and in SQL this would be trivial. I have two tables

TableOne = {{a, x1}, {b, x2}, {c, x3}};
TableTwo = {{a, y1}, {c, y2} , {a, y3}, {a, y4}, {b, y5}, {c,y6}, {c, y7}}

I want to abe able to join these two tables where the values of column 1 in both tables match such that:

DesiredResult =  {{a, x1, a, y1}, {c, x3 , c, y2} , {a, x1, a, y3}, {a, x1, a, y4}, {b, x2, b, y5}, {c, x3, c, y6}, {c, x3, c, y7}}

I've tried with both Select[] statements inside a Table[] structure and also looked into JoinAcross[] but haven't been able to acheive the desire effect. In SQL it would be simple, something like:

SELECT Col1.Table1, Col2.Table1, Col1.Table2, Col2.Table2,  FROM table2 INNER JOIN table1 ON Col1.Table1  = Col1.Table2

Or something similar.

$\endgroup$

4 Answers 4

8
$\begingroup$

I think you should stay away from associations because your keys aren't unique. Maybe just go over all tuples of elements and pick those that match your criterion:

Reap[Outer[If[#1[[1]] == #2[[1]], Sow@Join@##] &, TableOne, TableTwo, 1]][[2, 1]]

(*    {{a, x1, a, y1}, {a, x1, a, y3}, {a, x1, a, y4}, {b, x2, b, y5}, {c, x3, c, y2},
       {c, x3, c, y6}, {c, x3, c, y7}}    *)

Alternatively, construct a list of all tuples and then select: (this may use more memory if the lists are large)

Flatten /@ Select[Tuples[{TableOne, TableTwo}], #[[1, 1]] == #[[2, 1]] &]

(*    {{a, x1, a, y1}, {a, x1, a, y3}, {a, x1, a, y4}, {b, x2, b, y5}, {c, x3, c, y2},
       {c, x3, c, y6}, {c, x3, c, y7}}    *)
$\endgroup$
1
  • $\begingroup$ That'll do it, thanks again Roman. $\endgroup$
    – user27119
    Commented Aug 18, 2019 at 16:57
6
$\begingroup$
assocOne = AssociationThread[First /@ #, #] & @ TableOne;

f = Join[assocOne[First @ #], #]&;

Map[f] @ TableTwo  

{{a, x1, a, y1}, {c, x3, c, y2}, {a, x1, a, y3}, {a, x1, a, y4}, {b, x2, b, y5}, {c, x3, c, y6}, {c, x3, c, y7}}

$\endgroup$
1
  • $\begingroup$ Also a very nice solution, +1! $\endgroup$
    – user27119
    Commented Aug 18, 2019 at 22:13
3
$\begingroup$
Flatten[Table[Join[n, #] & /@ Cases[TableTwo, {n[[1]], _}], {n, TableOne}], 1]

  (* {{a, x1, a, y1}, {a, x1, a, y3}, {a, x1, a, y4}, {b, x2, b, y5}, {c, 
  x3, c, y2}, {c, x3, c, y6}, {c, x3, c, y7}} *)
$\endgroup$
1
  • $\begingroup$ So far you are winning on the compactness of the code! $\endgroup$
    – user27119
    Commented Aug 19, 2019 at 11:05
2
$\begingroup$

Another way:

Join[Extract[TableOne, #], TableTwo, 2] &[
  Lookup[PositionIndex[TableOne[[All, 1]]], TableTwo[[All, 1]]]]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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