How best to join tables, which have different lengths on the same column values which exist in both tables?

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.

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}}    *)

• That'll do it, thanks again Roman. – Q.P. Aug 18 at 16:57
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}}

• Also a very nice solution, +1! – Q.P. Aug 18 at 22:13
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}} *)

• So far you are winning on the compactness of the code! – Q.P. Aug 19 at 11:05

Another way:

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