5

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?

  • 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. – MaryC.fromNZ Nov 18 '14 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? – Jacob Jan Tuinstra Feb 5 '15 at 14:25
  • Sorry it's working again, no solutions yet appart from a specific code function – Emilio Nicolás Aug 29 '18 at 11:46
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.

2

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.

  • 1
    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 '18 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! – Fabricio Rocha Feb 16 '18 at 12:58
  • @FabricioRocha: Please take a look to my answer. – Rubén Feb 16 '18 at 15:42
  • Thank you all! do you have any example of this to understand it better? – Emilio Nicolás May 9 '18 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.
  • Thanks Rúben, I upvote your answer and will try soon, do you have any updates since you wrote it? – Emilio Nicolás May 9 '18 at 16:14
  • Noup. Do you have a suggestion to improve my answer? – Rubén May 9 '18 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 – Emilio Nicolás May 11 '18 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. – Rubén Aug 29 '18 at 14:16
  • 1
    @EmilioNicolás I included a formula using semicolons as argument separator. – Rubén Aug 29 '18 at 14:32

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.