4

I've got 2 tables and on a third table I want to have all unique combinations of table2.a, table1.b where table1.c==table2.c

SELECT table2.a, table1.b FROM table1 JOIN table2 WHERE table1.c=table2.c

In sql I would be using inner join or join (I think) but I can't seem to find a way to do this using google sheets query()

It doesn't need to join the actual keys together like I have in table3.a I have already added an arrayformula to handle that. The prefered output would be a 2d array that I can fill in table3.b:c

table1

a b c
  4 7
  5 8
  6 9 

table2

a b c
1   7
2   8
3   9

table3 (wanted output)

a  b  c
14 1  4 
25 2  5
36 3  6

I've tried to replicate what I would use in sql to get the results I want, but to no avail as the query() method doesn't work the same.

Things I have tried. =query("SELECT" table1!A, table2!A "FROM "table1, table2" WHERE" table1!C = table2!C) doesnt work and isn't the correct syntax

=query({table1!A2:A; table2!A2:A}) this gets me the list of both in one column,

=query({table1!A2:C; table2!A2:C}, "select Col1 where Col2='3'") this gets me a list of all but only checks a single column (first column from both sheets) I think this is getting closer if i can find a way to set it to check a certain column from each sheet rather than the default Col1, Col2 etc.

  • 1
    Instead of {table1!A2:C; table2!A2:C} you could put {table1!B2:C; table2!A2:A, table2!C2:C} to align the columns. I don't see how this helps with forming all combinations, though. – user135384 Nov 28 '16 at 22:07
2

I would write a custom function for this, to be used as

=combinations(table1!B2:B, table1!C2:C, table2!A2:A, table2!C2:C)

that is, the parameters are ValueColumn1, KeyColumn1, ValueColumn2, KeyColumn2.

The logic is entirely straightforward: run a double loop and collect whatever matches.

function combinations(range1, key1, range2, key2) {
  var output = [];
  if (range1.length != key1.length || range2.length != key2.length) {
    throw new Error('Each range must have the same length as its key'); 
  }
  for (var i = 0; i < range1.length; i++) {
    for (var j = 0; j < range2.length; j++) {
      if (key1[i][0] === key2[j][0] && key1[i][0] !== '') {
        output.push([range1[i][0], range2[j][0]]);
      }
    }
  }
  return output;
}
  • yea I figured I would end up needing to write a function for it, I was hoping that there would be a way to do it with the built in functions. thanks though :D – Nicholas Tsaoucis Nov 28 '16 at 22:44
  • 1
    If there is, it's more complicated than writing a custom function. Here you can see the code for generating all combinations of values in two columns, but that's without checking that the keys match. – user135384 Nov 28 '16 at 22:59

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.