I have two tables:
Table Y: indicates over what time period money is valid
x
+-----------+---------+------+
| starttime | endtime | name |
+-----------+---------+------+
| 1 | 2 | Mark |
| 3.4 | 3.6 | Fred |
| 2 | 2.2 | Fred |
+-----------+---------+------+
Table Z
+-----------+---------+------+-------+
| starttime | endtime | name | money |
+-----------+---------+------+-------+
| 1.5 | 1.7 | Mark | 0.1 |
| 1.4 | 3.5 | Fred | 0.2 |
| 0.9 | 1.0 | Fred | 0.3 |
| 1.2 | 2.5 | Fred | 0.5 |
+-----------+---------+------+-------+
What I want:
+------+---------------+
| name | sum_validmoney|
+------+---------------+
| Mark | 0.1 |
| Fred | 0.7 | **=> this is 0.2+0.5 (because those rows overlapped in start/endtime segments)**
+------+---------------+
I want the to sum up rows in Table Z based on time segments constraints (bounded) indicated in Table Y. That is, the rows that are to be summed in Table Z must have time segments within those found in Table Y (there are also additional filters on Table Z for other queries; but I don’t think that’s relevant here)
I’m very new to MySQL. I’ve tried subqueries, but think I’m missing something key; I’m not sure if this can be done without some complicated loop structure which mysql doesn’t seem well-equipped for.
Advertisement
Answer
It looks like you want to filter table z
on rows whose range overlap ranges at least one range in table y
.
If so, one approach is:
select name, sum(money) sum_valid_money
from z
where exists (
select 1
from y
where y.name = x.name and y.starttime <= x.endtime and y.endtime >= x.starttime
)
group by name