How to make SQL JOIN of two tables with Google Spreadsheet?

by Emilio Nicolás   Last Updated February 16, 2018 02:03 AM

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 work arround over this?

Example

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

There are users subscription information and deliver dates, I want to 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?

Tags : google-sheets


Answers 2


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.

Jacob Jan Tuinstra
Jacob Jan Tuinstra
November 20, 2014 21:43 PM

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 an 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 a 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 B")

And in Tab3!B1:

=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.

Regards,

Fabricio Rocha

Brasilia, Brasil

Fabricio Rocha
Fabricio Rocha
February 16, 2018 01:56 AM

Related Questions




Copying values between google sheets

Updated July 26, 2018 21:03 PM