Inner join with two SELECT queries

Question

I'm doing some online courses and am still very new to all this. I have two SELECT statements that work perfectly separately but when I add the INNER JOIN things just aren't working. I know I've got to be missing something so ridiculously simple but I'm just not seeing it.

Farm_prices column information

index       INTEGER NULLABLE    
date        DATE    NULLABLE    
cropType    STRING  NULLABLE    
GEO         STRING  NULLABLE    
pricePerMT  FLOAT   NULLABLE    

Monthly_FX column information

index       INTEGER NULLABLE    
date        DATE    NULLABLE    
FXUSDCAD    FLOAT   NULLABLE    

And the two select statements I'm trying toj join

SELECT * 
FROM `ibm-project-326221.IBM_project.MONTHLY_FX` 
WHERE date >= '2020-07-01'

SELECT * 
FROM `ibm-project-326221.IBM_project.farm_prices` 
WHERE cropType = 'Canola' 
  AND GEO = 'Saskatchewan' 
  AND date >= '2020-07-01'

Answer 1

Presumably, you want the FX rate for the corresponding date, so the join key should be date, not index:

SELECT fp.*, fx.*
FROM `ibm-project-326221.IBM_project.farm_prices` fp JOIN
     `ibm-project-326221.IBM_project.MONTHLY_FX` fx
     ON fp.date = fx.date
WHERE fp.cropType = 'Canola' AND
      fp.GEO = 'Saskatchewan' AND
      fp.date >= '2020-07-01'