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.

Improve this question
$\endgroup$
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}}    *)
Improve this answer
$\endgroup$
  • $\begingroup$ That'll do it, thanks again Roman. $\endgroup$ – Q.P. Aug 18 '19 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}}

Improve this answer
$\endgroup$
  • $\begingroup$ Also a very nice solution, +1! $\endgroup$ – Q.P. Aug 18 '19 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}} *)
Improve this answer
$\endgroup$
  • $\begingroup$ So far you are winning on the compactness of the code! $\endgroup$ – Q.P. Aug 19 '19 at 11:05
2
$\begingroup$

Another way:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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