I have two tables I am looking to join by their dates:

table 1: **reference**

```
| date           |
| -------------- |
| 2018-01-01     |
| 2018-01-02     |
| 2018-01-03     |
| 2018-01-04     |
| 2018-01-05     |
| 2018-01-06     |
| 2018-01-07     |
| 2018-01-08     |
| 2018-01-09     |
| 2018-01-10     |

```

table 2: **events**

```
| id | date       |
| -- | ---------- |
| aa | 2018-01-03 |
| bb | 2018-01-05 |
| cc | 2018-01-08 |

```

I am looking to join the two by their date fields to achieve the following results:

```
| id | reference date | event date |
| -- | -------------- | ---------- |
| aa | 2018-01-03     | 2018-01-03 |
| aa | 2018-01-04     | 2018-01-03 |
| bb | 2018-01-05     | 2018-01-05 |
| bb | 2018-01-06     | 2018-01-05 |
| bb | 2018-01-07     | 2018-01-05 |
| cc | 2018-01-08     | 2018-01-08 |
| cc | 2018-01-09     | 2018-01-08 |
| cc | 2018-01-10     | 2018-01-08 |

```

i.e for each event id, the reference date starts at the earliest event date for that id and ends when the next id kicks in. ( I hope i explained it well).

the code below works if I only have one event with one date. but with multiple events and start dates, it falls apart.

```
select
  events.id,
  reference.date,
  events.date
from events 
join reference on
  reference.date >= events.date 

```