I have a ROAD table that has a row for each vertex of each line:
+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
| 1 | 1 | 0 |
| 1 | 2 | 1.2 |
| 1 | 3 | 2.7 |
| 1 | 4 | 4.0 |
| 1 | 5 | 4.3 |
| 1 | 6 | 6.7 |
| 1 | 7 | 7.1 |
+---------+--------------+-------------+
| 2 | 1 | 0 |
| 2 | 2 | 5.2 |
| 2 | 3 | 7.7 |
| 2 | 4 | 9.6 |
| 2 | 5 | 11.8 |
+---------+--------------+-------------+
| 3 | 1 | 0 |
| 3 | 2 | 2.4 |
| 3 | 3 | 4.9 |
+---------+--------------+-------------+
And an EVENT table with rows that pertain to portions of roads:
+----------+---------+--------------+------------+
| EVENT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+----------+---------+--------------+------------+
| 01 | 1 | 2.8 | 5.9 |
| 02 | 2 | 3.0 | 5.6 |
| 03 | 3 | 0 | 2.8 |
+----------+---------+--------------+------------+
I want to select the vertices/rows from ROAD where the MEASURE_VAL contains the event FROM_MEASURE and TO_MEASURE (including the vertices in between):
+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
| 1 | 3 | 2.7 |
| 1 | 4 | 4.0 |
| 1 | 5 | 4.3 |
| 1 | 6 | 6.7 |
+---------+--------------+-------------+
| 2 | 1 | 0 |
| 2 | 2 | 5.2 |
| 2 | 3 | 7.7 |
+---------+--------------+-------------+
| 3 | 1 | 0 |
| 3 | 2 | 2.4 |
| 3 | 3 | 4.9 |
+---------+--------------+-------------+
It's kind of like I want to select vertices/rows from ROADS where the MEASURE_VAL is between the event FROM_MEASURE and TO_MEASURE, plus one vertex/row in both directions (where applicable).
How can I do this?