CODEWITHSUNDEEP
mysqlsqljoin
Jester
Jester

Reputation: 151

How to join two tables in mysql, but with a date difference between the two?

I have one table (GaugeCompare):

Form15 | Form30  | Form60  | GaugeDate
---------------------------------------
float  | caution | caution | 2018-10-08
float  | float   | caution | 2018-10-04
float  | caution | caution | 2018-10-02

And another table (DailyInterestRates):

Rate | RateDate
-----------------
...more data above...
4.875| 2018-09-23
4.875| 2018-09-22
4.75 | 2018-09-21
5    | 2018-09-20
5    | 2018-09-19
5    | 2018-09-18
5    | 2018-09-17
4.875| 2018-09-16
4.75 | 2018-09-15
4.875| 2018-09-14
...more data below...

I'm wanting to find a way to join these two tables based on a difference of where the dates are so many days apart by 15, 30 and 60 respectively. For example I would like to see my table look something like this:

Form15 | Form30  | Form60  | GaugeDate  | RateDate_15 | Rate_15 | RateDate_30 |...
-----------------------------------------------------------------------------------
float  | caution | caution | 2018-10-08 | 2018-09-23  | 4.875   | 2018-09-08  |...
float  | float   | caution | 2018-10-04 | 2018-09-19  | 5       | 2018-09-04  |...
float  | caution | caution | 2018-10-02 | 2018-09-17  | 5       | 2018-09-02  |...

I've been toying around with it most of the morning and doing searches on this, but haven't come across anything significant that would make this work for me. Anyone have any unique ways to approach this?

Upvotes: 0

Views: 83

Answers (3)

The Impaler
The Impaler

Reputation: 48770

You can do three outer joins, as in:

select
    g.Form15, g.Form30, g.Form60, g.GaugeDate,
    r15.RateDate as RateDate_15, r15.Rate as Rate_15,
    r30.RateDate as RateDate_30, r30.Rate as Rate_30,
    r60.RateDate as RateDate_60, r60.Rate as Rate_60
  from GaugeCompare g
  left join DailyInterestRates r15 on r15.RateDate = g.GaugeDate - interval 15 day
  left join DailyInterestRates r30 on r30.RateDate = g.GaugeDate - interval 30 day
  left join DailyInterestRates r60 on r60.RateDate = g.GaugeDate - interval 60 day

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Is this what you want?

select gc.*, dir_15.ratedate, dir_15.rate,
       dir_30.ratedate, dir_30.rate, dir_60.ratedate, dir_60.rate
from GaugeCompare gc left join
     DailyInterestRates dir_15
     on gc.gaugedate = dir_15.ratedate + interval 15 day left join
     DailyInterestRates dir_30
     on gc.gaugedate = dir_30.ratedate + interval 30 day left join
     DailyInterestRates dir_60
     on gc.gaugedate = dir_60.ratedate + interval 60 day;

Upvotes: 1

DanB
DanB

Reputation: 2039

select GaugeCompare.form15, GaugeCompare.form30, GaugeCompare.form60, GaugeCompare.gaugeDate,
    d15.ratedate ratedate_15, d15.rate rate_15,
    d30.ratedate ratedate_30, d30.rate rate_30,
    d60.ratedate ratedate_60, d60.rate rate_60
from GaugeCompare
        left join DailyInterestRates d15 on GaugeCompare.gaugeDate = date_add(d15.rateDate, interval 15 day)
        left join DailyInterestRates d30 on GaugeCompare.gaugeDate = date_add(d30.rateDate, interval 30 day)
        left join DailyInterestRates d60 on GaugeCompare.gaugeDate = date_add(d60.rateDate, interval 60 day);

Upvotes: 1

Related Questions