7

I am just figuring out how to achieve an apparently simple problem. I use to have data as in database tables in my spreadsheets. It's simple data but I often need to use =QUERY function and would be great if it could manage joins.

Any simple workaround over this?

Example

https://docs.google.com/spreadsheets/d/1YBf367DFhkwlnnsEuNxLoyWbtbo77KbH2VDEvtTf-zY/edit?usp=sharing

There are users subscription information and delivery dates, I want to CROSS JOIN both. In SQL would be something as simple as this:

SELECT dates.date, users.name
FROM dates, users 
WHERE dates.date BETWEEN users.date_begin AND users.date_end

How would you do that?

3
  • What would you do with the data that came back from the query? it's likely that there is an anternative spreadsheet focussed way to solve the underlying problem. Nov 18, 2014 at 14:53
  • 1
    The example data you provided isn't available anymore (now it's related to webapps.stackexchange.com/q/71165/29140). Btw, was my solution of use? Feb 5, 2015 at 14:25
  • Sorry it's working again, no solutions yet appart from a specific code function Aug 29, 2018 at 11:46

4 Answers 4

5

With the following little script you can do that.

Code

function mySQL(ref, dates) {
  var output = [];
  for(var i = 0, iLen = ref.length; i < iLen; i++) {
    var user = ref[i][0], begin = new Date(ref[i][1]), end = new Date(ref[i][2]);
    for(var j = 0, jLen = dates.length; j < jLen; j++) {
      var date = new Date(dates[j]);
      if(date >= begin && date <= end) {
        output.push([date, user]);
      }
    }
  }
  return output;  
}

Screenshot

data
enter image description here

outcome
enter image description here

Example

I've added the script to your example file.

0
3

I know this has been inactive for long, but I came across this post while looking for a "Google Sheets JOIN", and as I got something that worked for me, I think I might share it.

Albums: a list of Beatles albums, where column A has a unique ID for each row (primary key) and column B has the name of the album.

Songs: a sheet of Beatles songs. Every row contains in column A the ID of an album (a "foreign key", the same IDs found in Tab1), and the name of a song. In this sheet, you will find multiple rows with the same ID.

Tab3: is the sheet where you want to display in column A song names, in column B the album names, in column C the album ID.

So you have in Tab3!A1:

=QUERY(Songs!A1:B; "SELECT A, B")

And in Tab3!C1:

=ARRAYFORMULA({if(A1:A; vlookup(A1:A; Albums!A1:B; 2; FALSE); ) \ if(A1:A; vlookup(A1:A; Albums!A1:B; 1; FALSE); )})

Magic is made by the "grouping operators" "{}" and "\". It's a pity there is no way to make VLOOKUP return more than one column (well, I haven't found any). The IFs, as you see, only check if column A has a value to use for the lookup, and if there is none, it leaves the cells blank.

This is as close to an INNER JOIN as I could get... hope everyone can improve on it.

4
  • 2
    Vlookup can return multiple columns if you give it an array of column numbers like {3, 5, 6} instead of a single number.
    – user135384
    Feb 16, 2018 at 2:32
  • 1
    I wish I could upvote your tip! It´s a shame this is not told in the official Google VLOOKUP page! Feb 16, 2018 at 12:58
  • @FabricioRocha: Please take a look to my answer. Feb 16, 2018 at 15:42
  • Thank you all! do you have any example of this to understand it better? May 9, 2018 at 16:15
1

A CROSS JOIN returns the cartesian product of two tables. On my answer to Show all combinations for a selection of columns I explain how to do a cartesian product by using built-in functions.

Once we have the CROSS JOIN / CARTESIAN PRODUCT we could use QUERY to filter the array, but Google Query Language doesn't include the BETWEEN operator so we should use instead two conditions and the AND operator

Formula using commas as argument separator

=QUERY(ArrayFormula(SPLIT(
SORT(TRANSPOSE(         
       split(REPT(JOIN(",",TRANSPOSE(ID_1))&",",(COUNT(ID_1)*COUNTA(ID_2))/COUNTA(ID_1)),",")),1,TRUE)
&","&
TRANSPOSE(         
       split(REPT(JOIN("|",ARRAYFORMULA(QUERY(TRANSPOSE(table2&","),,2000000)))&"|",(COUNT(ID_1)*counta(ID_2))/COUNTA(ID_2)),"|"))
       ,",")),"select Col1,Col2 where Col1 > Col3 and Col1 < Col4 order by Col1")

Formula using semicolons as argument separator

=QUERY(ArrayFormula(SPLIT(
    SORT(TRANSPOSE(         
           split(REPT(JOIN(",";TRANSPOSE(ID_1))&",";(COUNT(ID_1)*COUNTA(ID_2))/COUNTA(ID_1));","));1;TRUE)
    &","&
    TRANSPOSE(         
           split(REPT(JOIN("|";ARRAYFORMULA(QUERY(TRANSPOSE(table2&",");;2000000)))&"|";(COUNT(ID_1)*counta(ID_2))/COUNTA(ID_2));"|"))
           ;","));"select Col1,Col2 where Col1 > Col3 and Col1 < Col4 order by Col1")

where

  • ID_1 is the named range for the dates sent table
  • table2 is the named range for the users table
  • ID_2 is the named range for the users column from the users table

NOTES:

  1. Before copy/paste any of the above formulas, create the named ranges.
8
  • Thanks Rúben, I upvote your answer and will try soon, do you have any updates since you wrote it? May 9, 2018 at 16:14
  • Noup. Do you have a suggestion to improve my answer? May 9, 2018 at 16:17
  • It's a pity Google doesn't give this functionallity. Your answer is great, but it's not possible to make a JOIN like SELECT * FROM table1 JOIN table 2 ON table1.date is BETWEEN table2.date_begin AND table2.date_end May 11, 2018 at 8:57
  • 1
    @EmilioNicolás Please note that the formula on my answer use commas as argument separator because I use Mexico as the regional setting. Your spreadsheet use Spain, so it use semicolons as argument separator. By the other hand, the sample data now include more data, User C and User X, but the expected result was not updated accordingly. Anyway, I will edit my answer to make it clearer. Aug 29, 2018 at 14:16
  • 1
    @EmilioNicolás I included a formula using semicolons as argument separator. Aug 29, 2018 at 14:32
0

I solved this by using JavaScript LINQ (Language Integrated Query).

It lets you specify JavaScript with complex join conditions. You can also perform other SQL queries such as Grouping, Projecting, Sorting and Filtering your sheets as if they were database tables. Look at the links below.

Note that in the LINQ query language I replaced all spaces in column names with underscores to make them valid JS identifiers.

https://docs.google.com/spreadsheets/d/1DHtQlQUlo-X_YVfo-Wo-b7315sSk2pxL5ci4Y9lxvZo/edit?usp=sharing

https://script.google.com/d/1R5L2ReHJrBRwyoSoVOFLzEQZiGtxidPfPkAeVownt7SWX6TpacY7gA7j/edit?usp=sharing

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.